Merging values in a table - 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 2000 Forums

SQL Server Development (2000)

Merging values in a table

Author

Topic

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-04-17 : 13:21:51

Hi,

I have a table whose values I would like to merge once 2 fields are the same. eg.

item no lot no quantity
a1 123 5
a1 456 6
a1 123 4

I would like to add the quantity of row3 to row 1 and then delete row 3.

Is this possible ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 13:45:26

just use


SELECT  itemno.lotno,sum(quantity) as qty
FROM table
GROUP BY itemno,lotno

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-04-17 : 15:48:02

Thanks. I have added the update statement but I am receiving an error incorrect syntax near ;

update iv00300 set qtyrecvd = qty from
(SELECT itemnmbr,lotnumbr,locncode,sum(qtyrecvd) as qty
FROM iv00300
GROUP BY itemnmbr,lotnumbr,locncode);

Also, I would like to update the value of the first instance of the same item number, lot number and location and delete all other occurences.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:27:04

do you have a unique valued column in your table which can be used to determine the order of occurance itemno, lotno value combination?

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-04-20 : 12:05:52

yes, there is a row id which is unique.

tracy5436
Yak Posting Veteran

50 Posts

Posted - 2009-04-21 : 13:45:42

There is a receipt date column which I would like to use. I would like to update the row which has the earliest receipt date with the sum of the lot quantity and then delete the other related rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources