How update only nomatch

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lenygold - 27 Sep 2008 02:14 GMT

Hello everybody!
I have 2 tables:
OLD_FAMILY

SSN NAME
----------------- -------------------
777777777 OLEG SMOLKIN
888888888 ALEX ORLOV
999999999 GREG BERG

NEW_FAMILY

SSN NAME
----------------- -------------------
777777777 OLEG SMOLKIN
888888888 ALEX ORLOV
999999999 MARK BERG

I want update name in new_table only when names nomacth (SSN - 999999999)

UPDATE NEW_FAMILY N
SET NAME = (SELECT A.NAME FROM OLD_FAMILY A
JOIN NEW_FAMILY
ON N.SSN = A.SSN
AND N.NAME<> A.NAME);
But i got an error
sqlcode: -811
SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES
INTO statement is more than one row.
Thank's in advance.

Reply to this Message

lenygold - 27 Sep 2008 02:57 GMT

Never mind i found the solution:
Update NEW_FAMILY as a
set NAME =
(select NAME from OLD_FAMILY as b
where b.SSN = a.SSN
and b.NAME <> a.NAME)
where exists
(select * from OLD_FAMILY as b
where b.SSN = a.SSN
and b.NAME <> a.NAME);

>Hello everybody!
>I have 2 tables:
[quoted text clipped - 26 lines]
>INTO statement is more than one row.
>Thank's in advance.

Reply to this Message