Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

update table with join

J1604Feb 24 2009 — edited Feb 24 2009
Hi
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
This post has been answered by Frank Kulash on Feb 24 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 24 2009
Added on Feb 24 2009
9 comments
561 views