CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Sumedha Vangury
Sumedha Vangury

Reputation: 693

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

Upvotes: 1

Views: 132

Answers (3)

Raghavi Kannan
Raghavi Kannan

Reputation: 67

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.

Upvotes: 0

Genish Parvadia
Genish Parvadia

Reputation: 1455

Try this query

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

Upvotes: 0

jayvee
jayvee

Reputation: 160

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

Upvotes: 1

Related Questions