Thursday, 19 September 2013

How to update table2 with table1's id only on a unique match

How to update table2 with table1's id only on a unique match

I have two tables: - table 1 with id, firstname and lastname - table 2
with t1_id, firstname and lastname
I'd like a SQL query (if possible, not a PL/SQL) to update table2 with
table1's id when I have a unique match on firstname and lastname
the word "unique" here is my problem :
update table2 t2
set t1_id = (select id from table1 t1
where t1.firstname=t2.lastname and t1.lastname=t2.lastname)
Whenever I have a match from t2 to multiple t1 records, I get the
"ORA-01427: single-row subquery returns more than one row" error. Any clue
to not update on multiple matches?
Thanks.

No comments:

Post a Comment