Merge duplicate rows - 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 2008 Forums

Transact-SQL (2008)

Merge duplicate rows

Author

Topic

Peter Smith
Starting Member

8 Posts

Posted - 2009-12-07 : 09:48:41

I have a table
[prospects]
id
companyname
address
city
zipcode
total_cars

Now in this table there are some duplicates

id companyname address city zipcode total_cars
1 test street NY 54543 NULL
2 test street NULL NULL 34
3 test street NY NULL NULL
4 test NULL NULL 54543 34

As you can see these 4 rows all contain a little bit of data. I want to merge these rows into 1 row with all available data:
test street NY 54543 34

The other rows should be deleted.

How? And preferably I dont want to have each rowname in my SQL statement (because the table has way more rows than im showing here), I'd like a dynamic statement that goes through all available table rows.

Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 10:15:01

select companyname, max(address),max(city),max(zipcode), max(total_cars) from your_table
group by companyname

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 10:21:57

SIMSALABIM

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-07 : 10:24:40

quote:
Originally posted by webfred

SIMSALABIM

No, you're never too old to Yak'n'Roll if you're too young to die.

What is it?

Madhivanan

Failing to plan is Planning to fail

Peter Smith
Starting Member

8 Posts

Posted - 2009-12-07 : 10:33:17

@madhivanan:
that selects a single row. But how do I update a single row with those values and how do I delete the other 3?

And what if I'd like to use dynamic column names? Now you explicitly use the column names:
max(address),max(city) etc...

Thanks again! :)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 10:54:13

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

SIMSALABIM

No, you're never too old to Yak'n'Roll if you're too young to die.

What is it?

Madhivanan

Failing to plan is Planning to fail

It is like Abracadabra - you know?
(spell - string of words with supposedly magic power)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-07 : 11:18:05

[code]
update t1
set address = dt.address,
city = dt.city,
... and so on ...
from your_table as t1
join
(
select
max(id) as id,
companyname,
max(address) as address,
max(city) as city,
max(zipcode) zipcode,
max(total_cars) as total_cars from your_table
group by companyname
)dt
on dt.id = t1.id
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Peter Smith
Starting Member

8 Posts

Posted - 2009-12-07 : 12:44:51

Ok, that does the merging trick :)
And how do I delete the other duplicate rows after the merge?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-08 : 01:23:59

quote:
Originally posted by Peter Smith

Ok, that does the merging trick :)
And how do I delete the other duplicate rows after the merge?

Is this one time update or the table data keeps on changing?

Madhivanan

Failing to plan is Planning to fail

eralper
Yak Posting Veteran

66 Posts

Posted - 2009-12-08 : 15:46:04

Hi Peter,
I used the new Merge command as a solution to your problem

With the data you supplied, the below query worked fine for me
Updated only one company record with existing values rather than null ones
Also the dublicate contents are deleted after they are used in the update

You can find examples at [url]http://www.kodyaz.com/articles/SQL2008-merge-command.aspx[/url]

MERGE prospects
USING
(
SELECT
max(id) id,
companyname,
max(address) address,
max(city) city,
max(zipcode) zipcode,
max(total_cars) total_cars
FROM prospects
GROUP BY companyname
) NewData ON prospects.id = NewData.id
WHEN MATCHED THEN
UPDATE SET
prospects.companyname = NewData.companyname,
prospects.address = NewData.address,
prospects.city = NewData.city,
prospects.zipcode = NewData.zipcode,
prospects.total_cars = NewData.total_cars
WHEN NOT MATCHED BY SOURCE THEN DELETE;
GO

-------------
Eralper
http://www.kodyaz.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources