在单个查询中更新和选择 [英] update and select in a single query

问题描述

我已经写了一个存储过程，我在更新语句中得到一个错误，请帮我修复它.谢谢.

I have written a stored procedure, I am getting an error in the update statement, please help me fix it. Thanks.

ALTER PROCEDURE [dbo].[UserTransac] 
@SponsorId varchar(20),
@UserId varchar(20),
@SponsorName varchar(50),
@Level int=1

AS
BEGIN
  if not exists(select User_Id from UserTransaction)
    insert into UserTransaction(Sponsor_Id,User_Id,Level_No,Sponsor_Name)
    values(@SponsorId,@UserId,@Level,@SponsorName)
  else
    insert into UserTransaction(Sponsor_Id,User_Id,Level_No,Sponsor_Name)
    values(@SponsorId,@UserId,@Level,@SponsorName)

  insert into UserTransaction(Sponsor_Id,User_Id,Level_No,Sponsor_Name)
  values(@SponsorId,@UserId,@Level+1,@SponsorName)

  update UserTransaction 
  set Sponsor_Id=select Sponsor_Id from Register where User_Id=@UserId

END

推荐答案

试试这个代码

UPDATE A
SET A.[Sponsor_Id] = B.[Sponsor_Id]
FROM [UserTransaction] A
INNER JOIN [Register] B ON A.[User_Id] = B.[User_Id]

您也可以在 INNER JOIN 后添加 WHERE 子句

you can also add WHERE clause after INNER JOIN

这篇关于在单个查询中更新和选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！