If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Updating Table using Alias column Name In Where Clause?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jul 2008
Posts: 91
Updating Table using Alias column Name In Where Clause?

SQL> Create table TEST_NEW (STATS VARCHAR2(50) ,
2 CODE VARCHAR2(5) ,
3 LOC VARCHAR2(2) ,
4 KLI VARCHAR2(5) ,
5 TYPE VARCHAR2(2) ,
6 PROPTION VARCHAR2(50) ,
7 ORDER_NO VARCHAR2(50) ,
8 PREPRO VARCHAR2(50) );

Table created.

SQL> Insert into TEST_NEW values('W', 'O', 'AA', '68', 'E', 'P1', '1', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'BB', '66', 'E', 'P1', '2', 'Y');

1 row created.

SQL> Insert into TEST_NEW values('W', 'O', 'BB', '67', 'E', 'P1', '3', 'Y');

1 row created.

Select STATS , TYPE , PROPTION,ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, PROPTION ORDER BY STATS) as TOSTART, ORDER_NO ,ROWNUM from SIMIS_DBA.TEST_NEW

Having the above query with the alias name Called TOSTART.

how to use TOSTART COlumn in Where clause when Updating.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Posts: 17
An analytical function can only be in the where outside of the main select, which you out in a subquery.
You could stick all this in a subquery and use it in your update.

UPDATE tab
SET col = col1
where tab1.order_no in (
select order_no
from (Select STATS,
TYPE ,
PROPTION,
ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
ORDER_NO ,
ROWNUM
from SIMIS_DBA.TEST_NEW
)
where tostart = 1)
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jul 2008
Posts: 91
Result Set of TOSTART Column itself i want to use in where clause.

update tab1 set col=col1 where TOSTART='1';

Since TOSTART is the alias form the select query.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Posts: 17
Sorry, you've lost me a bit there. My use of tab and cola has unnecessarily complicated it, apologies.

Using the correct column names, what are you hoping to update?
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jul 2008
Posts: 91
Introducing a new column i.e ALIAS in select query
Based on the ALIAS I have to update the table.

Update test_new set STATS='R' WHERE TOSTART=1;
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Posts: 17
UPDATE (
select STATS
from (Select STATS,
TYPE ,
PROPTION,
ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
ORDER_NO ,
ROWNUM
from SIMIS_DBA.TEST_NEW
)
where tostart = 1) T
SET T.STATS = 'R'

OR

UPDATE (
Select STATS,
TYPE ,
PROPTION,
ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
ORDER_NO ,
ROWNUM
from SIMIS_DBA.TEST_NEW
) T
SET T.STATS = 'R'
WHERE T.TOSTART = 1
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Jul 2008
Posts: 91
First query giving the error

ORA-01732: data manipulation operation not legal on this view

second :

ORA-01733: virtual column not allowed here

Calculated columns are not allowing to Update.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jul 2004
Posts: 17
UPDATE simis_dba.test_new
SET T.STATS = 'R'
WHERE order_no =
(
select order_no
from (Select STATS,
TYPE ,
PROPTION,
ROW_NUMBER() OVER(PARTITION BY STATS, TYPE, ROPTION ORDER BY STATS) as TOSTART,
ORDER_NO ,
ROWNUM
from SIMIS_DBA.TEST_NEW
)
where tostart = 1) T


Try him, if that doesn't work I'm not sure where to go next.
This is so much easier in sqlserver and teradata.

Hope this works for you
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On