CODEWITHSUNDEEP
sqlsql-serversql-server-2005
NSN
NSN

Reputation: 750

UPDATE using select statement and inner join

UPDATE TABLE1 set TABLE1.col1 = TABLE2.col1
FROM TABLE2 INNER JOIN TABLE3 ON COL2 = TABLE2.COL2
WHERE TABLE1.COL3 = TABLE3.COL3

Will the above query work if the JOIN returns multiple rows per condition? I am unable to update it, inspite of being able individually to Join Table3 and Table2.

Please help. I hope the information is sufficient.

Upvotes: 1

Views: 3016

Answers (3)

Martin Smith
Martin Smith

Reputation: 453338

As you are on SQL Server 2005 MERGE is not available to you.

To get a deterministic result you need a correlated subquery.

UPDATE TABLE1
SET    TABLE1.col1 = ISNULL((SELECT TOP (1) TABLE2.col1
                             FROM   TABLE2
                                    INNER JOIN TABLE3
                                      ON TABLE3.COL2 = TABLE2.COL2
                             WHERE  TABLE1.COL3 = TABLE3.COL3
                             ORDER  BY TABLE2.col1), TABLE1.col1)

Upvotes: 2

Oleg Dok
Oleg Dok

Reputation: 21766

No, you have to choose only 1 row from Table2 - sql server will choose the any suitable row from Table2 instead.

Upvotes: 1

gbn
gbn

Reputation: 432311

If there are multiple rows, then an arbitrary row will be used for the update. No error will be given. You can make it mandatory that one row is returned by the join by changing the UPDATE to a MERGE

Upvotes: 3

Related Questions