deleting duplicate
-
Hi all,
can anyone please tell me why its not working
table duplicate
col1 col2
1 abc
2 abc
1 abc
1 bcd
result(in oracle)
col1 col2
1 abc
2 abc
1 bcd
result(in sql server)
col1 col2
2 abc
1 bcd
below query is deleting duplicate records in oracle .but in sql server instead its deleting both the records
oracle:
DELETE FROM duplicate d1 WHERE 1<(select count(1) from duplicate d2 WHERE d1.col1=d2.col1 and 1.col2=d2.col2)
sqlserver:
DELETE d1 FROM duplicate d1 WHERE 1<(select count(1) FROM duplicate d2 WHERE d1.col1=d2.col1 and d1.col2=d2.col2)
-
Probably because Oracle is a sucky DB engine who evaluates the expression row by row, while SQL Server evaluates them all at the same time, resulting in both rows being deleted.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
You will need to change the query (yes SQL server and Oracle use different SQL language and execute it differently).
If you just want to delete on of the duplicate errors - do a inner join (you will need unique identity column though)
assuming you have table like
ID, col1, col2
where ID is a unique then you can do:
DELETE
FROM t1
INNER JOIN t2
ON t1.Col1=t2.Col1 and t1.Col2 = t2.Col2
WHERE and t1.ID<t2.ID -- this will keep the one duplicate
-
Here is another way which doesn't required a sequential identity column.
with MyCTE as
(
select col1, col2, ROW_NUMBER() over(PARTITION by col1, col2 order by (select null)) as RowNum
From SomeTable
)
DELETE MyCTE
where RowNum > 1
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Using ROW_NUMBER to do the duplicate deletes is another solution and I think can be more efficient in some cases. Search web for sql server row_number delete duplicates
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Sean Lange (8/8/2013)
Here is another way which doesn't required a sequential identity column.with MyCTE as
(
select col1, col2, ROW_NUMBER() over(PARTITION by col1, col2 order by (select null)) as RowNum
From SomeTable
)
DELETE MyCTE
where RowNum > 1
This is my preferred solution as well.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (8/8/2013)
Probably because Oracle is a sucky DB engine who evaluates the expression row by row, while SQL Server evaluates them all at the same time, resulting in both rows being deleted.Is this really how Oracle works? Any self-referencing DELETE (or UPDATE?) can therefore be non-deterministic?
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Mark-101232 (8/9/2013)
Koen Verbeeck (8/8/2013)
Probably because Oracle is a sucky DB engine who evaluates the expression row by row, while SQL Server evaluates them all at the same time, resulting in both rows being deleted.Is this really how Oracle works? Any self-referencing DELETE (or UPDATE?) can therefore be non-deterministic?
I said "probably" 😀
I have no knowledge of the internals of Oracle and I like to keep it that way.
But evaluating the OP's query row-by-row instead of set-based is the only explanation I can come up with. It must have something to do with the way both database engines execute the query, but you'd have to take a look at the query plans I suppose.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
it will be great if anyone explain or give some links to learn how sql server engine will process the query for some basic dml operations(delete,update,insert).
DELETE d1 FROM duplicate d1 WHERE 1<(select count(1) FROM duplicate d2 WHERE d1.col1=d2.col1 and d1.col2=d2.col2)
with MyCTE as
(
select col1, col2, ROW_NUMBER() over(PARTITION by col1, col2 order by (select null)) as RowNum
From SomeTable
)
DELETE MyCTE
where RowNum > 1
thanks
-
sathiyan00 (8/13/2013)
it will be great if anyone explain or give some links to learn how sql server engine will process the query for some basic dml operations(delete,update,insert).DELETE d1 FROM duplicate d1 WHERE 1<(select count(1) FROM duplicate d2 WHERE d1.col1=d2.col1 and d1.col2=d2.col2)
with MyCTE as
(
select col1, col2, ROW_NUMBER() over(PARTITION by col1, col2 order by (select null)) as RowNum
From SomeTable
)
DELETE MyCTE
where RowNum > 1
thanks
Man, answering that goes WAY beyond a forum post I think!! I recommend you get a book on sql server query plans (there are some free ebooks out there I think??) and show the actual query plan for both of those queries above (or any other's you are interested in) and use that as a learning tool.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply