delete duplicated data

Question

Post reply

delete duplicated data

ny66

Mr or Mrs. 500

Points: 549
More actions
August 12, 2010 at 6:42 am

#238845

i have a table that has duplicate receipt records in it. i would like to delete only one of the rows from the table, can anyone tell me how to do this, not sure of the syntax to use.
all the following records are duplicated, need to keep one row of the data
001 AAA 20100319 02940818839418 415 18 98152 100 M RECEIPT
001 AAA 20100319 02940818839418 415 18 98152 100 M RECEIPT
001 AAA 20100319 02940818840018 537 18 98152 100 L RECEIPT
001 AAA 20100319 02940818840018 537 18 98152 100 L RECEIPT
001 AAA 20100319 02940818868418 58 18 98161 960 L RECEIPT
001 AAA 20100319 02940818868418 58 18 98161 960 L RECEIPT
was thinking of deleting the record by row number but it needs to fit certain criteria, like these were all duplicated on the same day.
they have common values date, amount, style, color, season.
tried something like this, but im not sure if i use a query to get the specific rows the row number may not match the table row and i could delete the wrong row,
DELETE FROM [dbo.RECVSKU# ORDERED BY ROWID] WHERE RCDATE='20100319' and ROWID =1

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

;WITH OrderedRows AS (

SELECT RowNo = ROW_NUMBER() OVER (PARTITION BY acolumnlist ORDER BY acolumnlist)

FROM MyTableWithDupes

)

DELETE FROM OrderedRows WHERE RowNo > 1

Edited to show that the script includes DELETE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

crazy4sql SSCoach Points: 19590 More actions · Answer 2

if you have similar data and want only one record then why not take the distinct record to temp table and then delete all those record which are not in temp

----------
Ashish

ny66 Mr or Mrs. 500 Points: 549 More actions · Answer 3

If i create a table that contains my duplicate values, how do i delete them from the other table

not sure of the syntax to use

select * from duptable where rec=table.rec etc???

jerry-621596 SSCertifiable Points: 6096 More actions · Answer 4

If the rows are truely duplicates you could do:

INSERT INTO tempTbl

SELECT DISTINCT * FROM origTbl

And then delete everything from the first table and insert the distinct rows back in.

crazy4sql SSCoach Points: 19590 More actions · Answer 5

you can delete all the data by comparing to temp table and then as temptable have unique record you can import it back to original table

something like

select * from table where column in(select distinct(column) from table) will give you unique record.

If you agree with this then import them in temp table like

select * into temp from table where column in(select distinct(column) from table)

and then delete * from table. once all record gone from original table then

insert into table (column1,column2,......) values as

select column1,column2,......from temp.

hope it will help

----------
Ashish

ny66 Mr or Mrs. 500 Points: 549 More actions · Answer 6

i used delete top(1) from table etc.... that seemd to work

thanks