Update Table with itself - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update Table with itself

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-01-15 : 14:50:52

Working with one table IMINVLOC which holds inventory qty's and locations.

What I would like to do is take all of the qty's from location 2 and add them to location 1. All location 2 records have a location 1 record. Table would look something like this:

item_no loc QOH
abc 1 500
abc 2 400

My goal is to update abc's loc 1 to 900 for QOH

Not quite sure how to do this??

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-01-15 : 15:09:40

Tried something like this but Get a message that I must declare my variable on line 22.

begin
declare @imlocation table (
item_no char (15) not null,
loc char (3) not null,
Qty_on_hand decimal (9) not null,
rownum int IDENTITY(1,1) pRIMARY KEY NOT NULL
)

insert into @imlocation
select item_no, loc, Qty_on_hand
from iminvloc_sql
where loc ='2'

UPDATE iminvloc_sql
SET iminvloc_sql.qty_on_hand = @imlocation.qty_on_hand + iminvloc_sql.qty_on_hand
join @imlocation on @imlocation.item_no = iminvloc_sql.item_no
where iminvloc_sql.loc = '1'

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-15 : 15:16:07

[code]
update a
set QOH = b.QOH
from IMINVLOC a inner join IMINVLOC b
on a.item_no = b.item_no
and a.loc > b.loc[/code]

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-01-15 : 15:22:31

That looks like its just updating qoh form location 2 to 1. I want to ADD the QoH from location 2 to location 1.

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2009-01-15 : 15:31:06

[code]update a
set QOH =
case when a.loc = 1 then a.QOH+b.QOH
when a.loc = 2 then b.QOH end
from IMINVLOC a inner join IMINVLOC b
on a.item_no = b.item_no
and a.loc <> b.loc
[/code]

Vack
Aged Yak Warrior

530 Posts

Posted - 2009-01-15 : 15:48:35

That seemed to do the trick.

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 23:01:01

[code]
update a
set a.QOH = a.QOH+b.QOH
from IMINVLOC a inner join IMINVLOC b
on a.item_no = b.item_no
and a.loc =1
and b.loc=2
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources