CODEWITHSUNDEEP
sqloracle-database
cooldude
cooldude

Reputation: 125

UPDATE statement with SELECT

I am trying to write an UPDATE statement with SELECT statement in ORACLE but I kept getting "invalid identifier" error. I am sure that the field exists.

This is my SQL:

update 
(
select distinct a.item_id, a.account_code, b.item_id, b.account_code
from bp.poline a, mt.material b 
where a.item_id = b.item_id  
and a.account_code is not null and b.account_code is null
)
set b.account_code = a.account_code

And this is the error that I get:

Error report:
SQL Error: ORA-00904: "A"."ACCOUNT_CODE": invalid identifier
00904. 00000 -  "%s: invalid identifier

Upvotes: 0

Views: 2400

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

In Oracle you can update a subquery only if Oracle is able to locate precisely one and only one row of a base table for each row of the subquery. Furthermore, additional restrictions apply concerning the use of analytics function, aggregates, etc.

In your example the DISTINCT will make Oracle unable to update the subquery because one row of the subquery could point to several rows of the base table.

If you remove the DISTINCT, the query will work only if there is a unique index on MATERIAL(item_id) so that each row in the table POLINE can only be associated with at most one row in MATERIAL:

UPDATE (SELECT a.item_id, a.account_code acct_a, 
               b.item_id, b.account_code acct_b
          FROM bp.poline a, mt.material b
         WHERE a.item_id = b.item_id
           AND a.account_code IS NOT NULL
           AND b.account_code IS NULL)
   SET acct_a = acct_b

Updating a join is very efficient but has several restrictions, what if you don't have this index?

You could write a standard update with a different subquery:

UPDATE poline a
   SET a.account_code = (SELECT b.account_code
                           FROM material b
                          WHERE b.item_id = a.item_id
                            AND b.account_code is not null)
 WHERE a.account_code IS NULL
   AND a.item_id IN (SELECT b.item_id 
                       FROM material b
                      WHERE b.account_code IS NOT NULL)

The most elegant solution IMO however, inspired by an answer to a similar question, would be:

MERGE INTO (SELECT * FROM a WHERE account_code IS NULL) a
     USING (SELECT * FROM b WHERE account_code IS NOT NULL) b
        ON (a.item_id = b.item_id)
WHEN MATCHED THEN UPDATE SET a.account_code = b.account_code;

Upvotes: 4

user1102001
user1102001

Reputation: 707

to use update statement with select statement in SQL below is the syntax...and its working in my code in this i am using derived table may be this vl help you

for ex....

UPDATE SHIFT_MST SET SHIFT_MST.SHIFT_DESC=A.SHIFT_DESC,SHIFT_MST.SHIFT_CODE=A.SHIFT_CODE from

(SELECT * FROM TEMP_SHIFT_MST)a

where a.SHIFT_ID=SHIFT_MST.SHIFT_ID

Upvotes: 0

Related Questions