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

Question

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.

András Ottó · Accepted Answer · 2012-11-15 15:42:44Z

3

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

answered Nov 15, 2012 at 15:42

András Ottó

7,7351 gold badge30 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

András Ottó Over a year ago

If the group has more ID, then the latest selected one will be used as @oldId.

bummi · Accepted Answer · 2012-11-15 16:17:29Z

0

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

edited Nov 15, 2012 at 16:17

answered Nov 15, 2012 at 15:58

bummi

27.4k14 gold badges67 silver badges105 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related