CODEWITHSUNDEEP
sqlsql-server
webnoob
webnoob

Reputation: 15934

Deleting / Updating x out of x+1 duplicate rows in a database

Note: If possible, I am looking for a pure SQL query solution so I can run it in SQL Server Manager.

I had an error in my script which mean that some data that was meant to be entered once has been entered multiple times and now I need a way to either delete all but 1 entry PER company_id field or update the export column for 1 entry per company_id where:

short_desc the has more than one occurrence and long_desc has more than one occurrence

My Data is structured like so:

   id  |  company_id  |  short_desc  |  long_desc  | export
    1       1234            word           text       0
    2       1234            word           text       0
    3       1234            word           text       0
    4       1234            word           text       0
    5       1234            word           text       0
    6       1234            word           text       0
    7       5678            another        foo        0
    8       5678            another        foo        0
    9       5678            another        foo        0
    10      5678            another        foo        0
    11      5678            another        foo        0
    12      5678            another        foo        0

I don't mind which entries are updated / deleted as long as I only have 1 of each so in the example above, I would only have 2 entries left / marked as export=-1.

I have tried doing various things with sub selects and group by but I always need to end up grouping on id as well so I never get the duplicates. I don't know if has just been a long morning but I can't seem to think of a pure SQL way to do this and I would like to try avoiding writing a script to do it.

If the export flag is set, I will just delete where it equals 0.

I am using SQL Server 2008.

Upvotes: 0

Views: 49

Answers (1)

hkutluay
hkutluay

Reputation: 6944

update table set export = -1
where table.id in 
(
  select min(id) from table group by company_id,short_desc,long_desc
  having count(1) > 1
)

Upvotes: 1

Related Questions