CODEWITHSUNDEEP
sqlsql-server
user517406
user517406

Reputation: 13773

sql update with a join

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 <> '--'

Upvotes: 0

Views: 108

Answers (2)

Thinhbk
Thinhbk

Reputation: 2214

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.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453243

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.

Upvotes: 1

Related Questions