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

回答1:

try this code

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

you can also add WHERE clause after INNER JOIN

回答2:

Try this query

update UserTransaction  
set Sponsor_Id = 1 
from Register  
where User_Id = @User_Id

回答3:

you just have to put the select within braces. Try this.

update UserTransaction set Sponsor_Id=(select Sponsor_Id from Register where User_id=@User_id)

Although this will update Sponsor_Id of all the records in UserTransaction. Make sure you use appropriate where clause to update only selected records. Hope this helps.