update table with join
J1604Feb 24 2009 — edited Feb 24 2009Hi
I have two tables and wanted to update one table based on join where one of the joined table has null values.
Something like this:
Table A
CLNT STAT GS LV
111 A 1 10
222 A 2 11
333 A 3 12
Table B
CLNT STAT GS LV END_DT
111 A 1 10 null
222 A 2 11 null
333 A 3 12 null
444 A 4 12 null
555 A 4 12 null
I want to update "Table B" for last two records where CLNT in (444,555)
SET status = 'D' and end_dt = sysdate for those two records which being updated.
I am joining two tables based on left outer join Table_A.CLNT (+) = Table_b.CLNT
update Table_B
set stat = (select nvl(t.stat,'D') from Table_B t, Table_A s where t.CLNT= s.CLNT (+))
/
Returned my error:
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
any help is highly appreciated.
Thanks
Jp