如何根据条件更新表列？

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.

StoreInfo上的UserId当前为空。如何使用UserInfo的UserId基于StoreInfo的UserName更新StoreInfo的UserId,并且Password与UserInfo中的UserName和Password匹配。

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.

以下是我写的用UserInfo中的第一个UserId更新StoreInfo中的整个UserId的查询,所以我知道这是错误的。

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

3 个解决方案

#1

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

#2

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

最有效的方法是UPDATE ... FROM语法,例如

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

#3

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!)

这将更新表中未设置UserId的所有行。如果您只想更新选定的行,请构建where子句。 (我没有测试过这个,所以请注意拼写错误!)

#1