How do i update a table column based on a condition?

Question

Two table:

StoreInfo:
UserId uniqueidentifier
StoreNo nvarchar
UserName nvarchar
Password nvarchar

UserInfo:
UserId uniqueidentifier
UserName nvarchar
Password nvarchar

the UserId on StoreInfo is currently null. How do i update StoreInfo's UserId with UserInfo's UserId based on StoreInfo's UserName and Password is match to the UserName and Password from UserInfo.

the following is the query that i wrote which update the entire UserId in StoreInfo with the first UserId from UserInfo so i know it's wrong.

declare @UserName nvarchar(255)
declare @Password nvarchar(25)
declare @UserId uniqueidentifier

select @UserName = UserName, @Password = Password, @UserId = UserId
from UserInfo

select UserId, Password 
   from FranchiseInfo 
   where UserID = @UserName and Password = @Password

update FranchiseInfo
set UserI = @UserId

Answer 1

UPDATE StoreInfo set UserId = ui.UserId from StoreInfo si inner join UserInfo ui on ui.UserName = si.UserName and ui.Password = si.Password where si.UserId is null

This will update all rows in the table where UserId is not set. Build out the where clause if you only want to update selected rows. (I haven't tested this, so watch for typos!)

[That reads a lot better when spread across multiple lines in Courier...]

Answer 2

The update would look like this

update storeinfo
set userid = u.userid
from userinfo u 
inner join storeinfo s on (s.username = u.username and s.password = u.password)
where userid is null

Answer 3

The most efficient way is the UPDATE ... FROM syntax, e.g.

UPDATE StoreInfo
SET
    UserId = ui.UserId
FROM
    StoreInfo si
    INNER JOIN UserInfo ui ON ui.UserName = si.UserName AND ui.Password = si.Password;

Answer 4

Premature optimization is the root of all evil in programming.