UPDATE statement with SELECT

Question

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

Doesn't it imply that `account_code` is not a field in your alias `a` ? — Hanky Panky, Feb 20 '13 at 09:42
See this related question: [Oracle - Update statement with inner join](http://stackoverflow.com/q/2446764/851811). — Xavi López, Feb 20 '13 at 09:53

score 4 · Answer 1 · edited May 23 '17 at 11:54

4

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;

edited May 23 '17 at 11:54

Community

1
1

answered Feb 20 '13 at 09:50

Vincent Malgrat

66,725
9
119
171

Thanks. I had tried to remove the DISTINCT syntax, but I am still getting the same invalid identifier error. I am not able to create unique index on the table. So is there any way that I can write the query? – cooldude Feb 20 '13 at 09:53
@cooldude: this is not the only thing I changed, look more closely :) – Vincent Malgrat Feb 20 '13 at 09:54
You mean the alias too? I had included the alias and removed the distinct. – cooldude Feb 20 '13 at 09:55
If it's helpful to you guys here's a [SQLFiddle](http://sqlfiddle.com/#!4/fd015/1). – Xavi López Feb 20 '13 at 09:56
@VincentMalgrat, I saw my mistake. I should just use the alias at the SET commands. I had changed that and I am no longer getting the invalid identifier error, but I am getting a different error now, which is: 'Error report: SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table 01779. 00000 - "cannot modify a column which maps to a non key-preserved table" *Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. *Action: Modify the underlying base tables directly.' – cooldude Feb 20 '13 at 09:58
See my updated answer, you can use a standard update with a subquery ([SQLFiddle](http://sqlfiddle.com/#!4/f097a/1)). @xavi thanks for the SQLFiddle ! – Vincent Malgrat Feb 20 '13 at 10:01

score 0 · Answer 2 · answered Feb 20 '13 at 09:46

0

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

answered Feb 20 '13 at 09:46

user1102001

689
2
10
21

CHECK THIS LINK IT WILL HELP YOU http://dba.stackexchange.com/questions/3033/how-to-update-a-table-from-a-another-table – user1102001 Feb 20 '13 at 10:00

UPDATE statement with SELECT

2 Answers2