Updating records to match existing records

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

Transact-SQL (2000)

Updating records to match existing records

Author

Topic

DazlerD
Starting Member

8 Posts

Posted - 2007-12-08 : 10:16:34

Hi

I have a database table that looks like the following:

id, reference, localnotenumber, quantity, price

The localnotenumber should be the same for each distinct reference but it isn't. This is so the records can be grouped by reference and made into a single row.

e.g.
1 CD10001 d091207-10001 2 3.99
2 CD10001 d091207-10001 1 4.99

grouped into 1 record using sum() as

CD10001 d091207-10001 3 8.98.

This record is then used in a report.
My localnotenumber is always different so the group by doesn't create a single record. What sql statement can I run to change the localnotenumber of the first record into the last record where the reference is the same?

id, reference, localnotenumber, quantity, price
1 CD10001 d091207-10001 2 3.99
2 CD10001 d091207-10002 1 4.99
3 CD10002 d091207-10003 2 5.99
4 CD10002 d091207-10004 1 6.99
5 CD10002 d091207-10005 2 7.99
6 CD10003 d091207-10006 1 8.99

Needs to be changed to
1 CD10001 d091207-10002 2 3.99
2 CD10001 d091207-10002 1 4.99
3 CD10002 d091207-10005 2 5.99
4 CD10002 d091207-10005 1 6.99
5 CD10002 d091207-10005 2 7.99
6 CD10003 d091207-10006 1 8.99

so when used with "group by" it can grouped into a single record.

Thanks

Darren

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-08 : 23:52:46

You don't need to change the localnotenumber to get your desired results. We can just use a derived table to get the data elements without localnotenumber, then join back to the main table and grab the minimum localnotenumber.


select t2.reference, min(t1.localnotenumber), t2.quantity, t2.price
from table1 t1
inner join
(
	select reference, sum(quantity) as quantity, sum(price) as price
	from table1
	group by reference
) t2
on t1.reference = t2.reference
group by t2.reference, t2.quantity, t2.quantity, t2.price

Here's my test case:


create table table1 (id tinyint, reference char(7), localnotenumber char(13), quantity tinyint, price decimal(3,2))

insert into table1 values(1, 'CD10001', 'd091207-10001', 2, 3.99)
insert into table1 values(2, 'CD10001', 'd091207-10002', 1, 4.99)
insert into table1 values(3, 'CD10002', 'd091207-10003', 2, 5.99)
insert into table1 values(4, 'CD10002', 'd091207-10004', 1, 6.99)
insert into table1 values(5, 'CD10002', 'd091207-10005', 2, 7.99)
insert into table1 values(6, 'CD10003', 'd091207-10006', 1, 8.99)

select t2.reference, min(t1.localnotenumber), t2.quantity, t2.price
from table1 t1
inner join
(
	select reference, sum(quantity) as quantity, sum(price) as price
	from table1
	group by reference
) t2
on t1.reference = t2.reference
group by t2.reference, t2.quantity, t2.quantity, t2.price

drop table table1

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DazlerD
Starting Member

8 Posts

Posted - 2007-12-08 : 23:58:40

Hi mate

Thanks for your reply

I do need to change the existing table as the stored procedure that runs the reports uses this table. And, it should have been right to start with, the Import routine was wrong in checking for the reference number.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-09 : 13:51:51

Here ya go:


update t1
set localnotenumber = t2.localnotenumber
from table1 t1
inner join
(
	select reference, min(localnotenumber) as localnotenumber
	from table1
	group by reference
) t2
on t1.reference = t2.reference and t1.localnotenumber <> t2.localnotenumber

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

DazlerD
Starting Member

8 Posts

Posted - 2007-12-09 : 14:46:09

Hi tkizer

Thanks very much for that response. I managed to sort it, not as elegantly as you have specified as I only wanted to be sure to update things that needed to be updated.

I filled a new db table with the reference numbers and the max localnotenumnber and then updated the primary table based on this.

The import routine messed up on 1652 records which I now have a record of. These have all been updated.

Thanks again for your help.

Darren

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources