sql - UPDATE statement with SELECT -

sql - UPDATE statement with SELECT -

i trying write update statement select statement in oracle kept getting "invalid identifier" error. sure field exists.

this sql:

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

and error get:

error report: sql error: ora-00904: "a"."account_code": invalid identifier 00904. 00000 - "%s: invalid identifier

in oracle can update subquery if oracle able locate exactly one , 1 row of base of operations table each row of subquery. furthermore, additional restrictions apply concerning utilize of analytics function, aggregates, etc.

in illustration distinct create oracle unable update subquery because one row of subquery point several rows of base of operations table.

if remove distinct, query work if there unique index on material(item_id) each row in table poline can associated @ 1 row in material:

update (select a.item_id, a.account_code acct_a, b.item_id, b.account_code acct_b bp.poline a, mt.material b a.item_id = b.item_id , a.account_code not null , b.account_code null) set acct_a = acct_b

updating bring together efficient has several restrictions, if don't have index?

you write standard update different subquery:

update poline set a.account_code = (select b.account_code material b b.item_id = a.item_id , b.account_code not null) a.account_code null , a.item_id in (select b.item_id material b b.account_code not null)

the elegant solution imo however, inspired answer similar question, be:

merge (select * account_code null) using (select * b account_code not null) b on (a.item_id = b.item_id) when matched update set a.account_code = b.account_code;

sql oracle