WHILE LOOP - 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

SQL Server 2005 Forums

Transact-SQL (2005)

WHILE LOOP

Author

Topic

AmandaS
Starting Member

2 Posts

Posted - 2011-05-16 : 12:39:49

I have 2 temp tables. I need to update table 2 with values from table 1. I was told to do a loop. Here is what I have:

table1:DECLARE @t_pt_repack TABLE
(
[unique_id] [bigint] NOT NULL IDENTITY(1, 1),
[wh_id] varchar(10) NULL,
[order_number] varchar(30) NULL,
[item_number] varchar(30) NULL,
[lot_number] varchar(15) NULL,
[carton_picked] BIGINT NULL,
[grade] [char] (1) NULL
)
table2:DECLARE @t_picked TABLE
(
[unique_id] [bigint] NOT NULL IDENTITY(1, 1),
[wh_id] varchar(10) NULL,
[order_number] varchar(30) NULL,
[hu_id] VARCHAR(22) NULL,
[item_number] varchar(30) NULL,
[lot_number] varchar(15) NULL,
[carton_picked] BIGINT NULL,
[pallet_type] [char] (1) NULL,
[grade] [char] (1) NULL
)

I need to update the top 1 carton_picked in @t_picked by summing carton picked from @t_pt_repack and carton picked from @t_picked joining on order_number, item_number and lot_number.

Here is my data in @t_picked

1 A OR01143550 A000108120 O035694R T2000174538 7 C U
2 A OR01143550 A000108124 O035694R T2000174538 19 C U
3 A OR01143550 A000108136 O035694R T2000174538 48 C U
4 A OR01143550 A000110397 O035694R T2000175279 3 W U
5 A OR01143550 A000110482 O035694R T2000175373 9 C U

Here is my data in @t_pt_repack
1 A OR01143550 O035694R T2000174538 -1 U
2 A OR01143550 O035694R T2000175373 -1 U

These are the results I would like to see in @t_picked after updating the carton_picked:
1 A OR01143550 A000108120 O035694R T2000174538 6 C U
2 A OR01143550 A000108124 O035694R T2000174538 19 C U
3 A OR01143550 A000108136 O035694R T2000174538 48 C U
4 A OR01143550 A000110397 O035694R T2000175279 3 W U
5 A OR01143550 A000110482 O035694R T2000175373 8 C U

I haven't had any formal training in SQL, been working as an assistant for almost a year, so sorry if I dont make much sense. I have never done a loop, any help at all would be greatly appreciated

AmandaS
Starting Member

2 Posts

Posted - 2011-05-16 : 12:41:31

This is in a sproc that runs every time an order ships, I am just working on one order for my example.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-16 : 13:42:07

quote:
Originally posted by AmandaS

This is in a sproc that runs every time an order ships, I am just working on one order for my example.

how does that work? I was gonna suggest a trigger

post your current code and ddl

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

MSquared
Yak Posting Veteran

52 Posts

Posted - 2011-05-17 : 15:02:50

You can do this in a set-based operation, just be careful that you don't run it twice against the same data since it will decrement over and over again

DECLARE @t_pt_repack TABLE
(
[unique_id] [bigint] NOT NULL ,
[wh_id] varchar(10) NULL,
[order_number] varchar(30) NULL,
[item_number] varchar(30) NULL,
[lot_number] varchar(15) NULL,
[carton_picked] BIGINT NULL,
[grade] [char] (1) NULL
)

DECLARE @t_picked TABLE
(
[unique_id] [bigint] NOT NULL,
[wh_id] varchar(10) NULL,
[order_number] varchar(30) NULL,
[hu_id] VARCHAR(22) NULL,
[item_number] varchar(30) NULL,
[lot_number] varchar(15) NULL,
[carton_picked] BIGINT NULL,
[pallet_type] [char] (1) NULL,
[grade] [char] (1) NULL
)
insert into @t_picked values
(1 ,'A','OR01143550','A000108120','O035694R','T2000174538','7','C','U'),
(2 ,'A','OR01143550','A000108124','O035694R','T2000174538','19','C','U'),
(3 ,'A','OR01143550','A000108136','O035694R','T2000174538','48','C','U'),
(4 ,'A','OR01143550','A000110397','O035694R','T2000175279','3','W','U'),
(5 ,'A','OR01143550','A000110482','O035694R','T2000175373','9','C','U')

insert into @t_pt_repack values
(1 ,'A','OR01143550','O035694R','T2000174538','-1','U'),
(2 ,'A','OR01143550','O035694R','T2000175373','-1','U')

-- data before update
select * from @t_picked
select * from @t_pt_repack

update p
set p.carton_picked = p.carton_picked + r.carton_picked
from @t_picked p inner join @t_pt_repack r
on p.order_number = r.order_number
and p.item_number = r.item_number
and p.lot_number = r.lot_number

-- data after update
select * from @t_picked

For Faster results please follow the posting guidelines here

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-17 : 16:20:09

See...this is derived data, and I would suggest that this is no good.

You should think like an accounting Ledger, then it would be just a matter of creating a view or something to sum everything up

Is your data model in production, dev, or is it school work?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources