sql update with a join

Question

Which is the correct way to do this update, is it this way :

UPDATE [log].codesEntered SET [log].codesEntered.countrycode = (SELECT [user].[profile].countryCode FROM [user].[profile] INNER JOIN [log].codesEntered ON [log].codesEntered.userid = [user].[profile].userid WHERE [log].codesEntered.countryCode <> [user].[profile].countryCode AND [log].codesEntered.campid = @campid AND [log].codesEntered.portalid = @portalid AND [user].[profile].countryCode <> '' AND [user].[profile].countryCode <> '--')

or this way :

UPDATE [log].codesEntered SET [log].codesEntered.countrycode = [user].[profile].countryCode FROM [log].codesEntered INNER JOIN [user].[profile] ON [log].codesEntered.userid = [user].[profile].userid WHERE [log].codesEntered.countryCode <> [user].[profile].countryCode AND [log].codesEntered.campid = @campid AND [log].codesEntered.portalid = @portalid AND [user].[profile].countryCode <> '' AND [user].[profile].countryCode <> '--'

second option looks pretty standard in terms of syntax - MSDN — whytheq
– whytheq, Commented Jun 28, 2012 at 9:23
possible duplicate of How can I do an UPDATE statement with JOIN in SQL? — mechanical_meat
– mechanical_meat, Commented Jun 28, 2012 at 23:28

Martin Smith · Accepted Answer · 2012-06-28 07:32:56Z

Assuming SQL Server the second one is not standard SQL and can cause undeterministic results in the event that multiple joined rows can match a codesEntered row.

The correlated sub query version would raise an error in that event. It would also set countrycode to NULL in the event there was no match at all.

The join might be more efficient.

You could also look at MERGE rather than the proprietary UPDATE ... FROM syntax if you are on at least SQL Server 2008. This will raise an error if there is an attempt to update the same row more than once.

+1 totally agree - if the sub-query returns more than one option then it hits a problem: option 2 is standard issue ...go with that

3 revs · Accepted Answer · 2012-06-28 09:40:00Z

You can try s.t like this (my prefer way):

UPDATE [log].codesEntered log SET [log].codesEntered.countrycode = (select countryCode from [user].[profile] where [user].[profile].userid = log.userid) WHERE log.userid in ( select [log].codesEntered.userid from [log].codesEntered join [user].[profile] on [log].codesEntered.userid = [user].[profile].userid where [log].codesEntered.countryCode <> [user].[profile].countryCode AND [log].codesEntered.campid = @campid AND [log].codesEntered.portalid = @portalid AND [user].[profile].countryCode <> '' AND [user].[profile].countryCode <> '--' )

This approach also works on other DMBS like oracle.

your last sentence seems to imply using FROM in an UPDATE query is wrong - I use it a lot - MSDN

Collectives™ on Stack Overflow

sql update with a join

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related