CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Jackson Tale
Jackson Tale

Reputation: 25842

How can I get a value from a select and use it as a variable in sql?

I am writing a sql proc with two parameters: @id and @id_group.

Consider this table group_info

ID   ID_Group    SupercededBy   Superceded   status
1    1           null           null         H
2    1           null           null         U

Given these parameters:

@id = 2 @id_group = 1

The first thing I need to do is to set the row’s SupercededBy to @id, where ID=@id and id_group=@id_group and status='H'.

Here is the statements I have written:

  update group_info
      set supercededby = @id
      where id_group=@id_group and status='H'

The second thing I need to do is to set the row’s Superceded to the id whose SupercededBy has been just updated from the above statements.

So in this example, row 2’s Superceded should be updated to 1.

But how to write the statements? I guess the statements might be something like this:

update group_info
      set superceded = **old_id**
      where id=@id

I know how to get the old_id, here it is

select id
    from group_info
    where id_group=@id_group and status='H'

But how can I use the values in the above select and insert the value to the update statements as old_id?

The final table should be

ID   ID_Group    SupercededBy   Superceded   status
1    1           2              null         H
2    1           null           1            U

I am using MS SQL Server.

Upvotes: 0

Views: 120

Answers (2)

bummi
bummi

Reputation: 27384

with this definition 

The second thing I need to do is to 
set the row’s Superceded to the id whose SupercededBy has been 
just updated from the above statements.

it's a massoperation

update group_info
set supercededby = @id
where id_group=@id_group and status='H'


Select ID,supercededby  
into #tmp
from group_info
where id_group=@id_group and status='H'



update group_info
set superceded = #tmp.ID
from #tmp 
where group_info.ID=#tmp.supercededby

Drop table #tmp

Upvotes: 0

András Ottó
András Ottó

Reputation: 7695

A SELECT @variable = columnName FROM ... statement can be used like this:

DECLARE @oldId INT

select @oldId  = id
    from group_info
    where id_group=@id_group and status='H'

update group_info
      set superceded = @oldId 
      where id=@id

Upvotes: 3

Related Questions