CODEWITHSUNDEEP
sqlsql-serverduplicatesrows
Tuğhan Avcı
Tuğhan Avcı

Reputation: 23

How to Update duplicated Rows into One Row

I need to merge same entries and sum quantity row by quantity row. For example:

glass type  height   width    quantity
---------------------------------------
4DC          1500     600        1
4DC          1500     600        2
4DC          1200     500        5
4DC          1200     500        2
3DC          1500     600        2

will be:

glass type   height   width   quantity
---------------------------------------
4DC           1500     600      3
4DC           1200     500      7
3DC           1500     600      2

But I don't want any select query, I need to update table and delete duplicate rows and update one of them with sum quantity.

How can I do it?

Upvotes: 0

Views: 361

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

My recommendation is to replace the table:

select glasstype, height, width, sum(quantity) as quantity
into temp_t
from t
group by glasstype, height, width;

truncate table t;  -- backup first!

insert into temp_t (glasstype, height, width, quantity)
    select glasstype, height, width, quantity
    from temp_t;

drop table temp_t;

Alternatively, you could do this in two steps:

with toupdate as (
      select t.*, sum(quantity) over (partition by glasstype, height, width) as new_quantity
      from t
     )
update toupdate
    set quantity = new_quantity;

with todelete as (
      select t.*,
             row_number() over (partition by glasstype, height, width order by glasstype) as seqnum
      from t
     )
delete from todelete
    where seqnum > 1;

Upvotes: 2

Thom A
Thom A

Reputation: 95561

I would do a similar thing to Gordon, however, I'd rename the objects instead:

SELECT GlassType,
       Height,
       Width,
       SUM(Quantity)
INTO dbo.NewTable
FROM dbo.YourTable
GROUP BY GlassType,
         Height,
         Width;
GO
EXEC sp_rename N'dbo.YourTable',N'OldTable';
GO
EXEC sp_rename N'dbo.NewTable',N'YourTable';
GO

This means you still have a copy of your old table, which you won't be able to TRUNCATE if you have any foreign keys on. YOu will, however, have to recreate any existing constraints and indexes on the new YourTable

I would then create a Unique Constraint on your table, so that you can't end up with duplicates in the future.

ALTER TABLE dbo.YourTable ADD CONSTRAINT UC_TypeHeightWidth UNIQUE (GlassType,Height,Width);

Upvotes: 1

Related Questions