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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-09-19 : 09:10:37
|
| Ray writes "The challenge - convert the following SELECT into a deletewithout using subqueries (for portability, etc.).select s1.ID, s2.dupdata from SalesDetails as s1, SalesDetails as s2 where (s1.dupdata=s2.dupdata) AND (s1.ID>s2.ID);(dupdata is the column(s) that contain duplicated data)If MS were nice to us, we could do:delete from SalesDetails as s1, SalesDetails as s2 where (s1.dupdata=s2.dupdata) AND (s1.ID>s2.ID);The single SELECT statement will display duplicates that need to be deleted. It will do it without subqueries.This provides the additional advantage of making it portable to other SQLs, like MySQL.Can we use the same idea to make a single statement "dupe delete"without subqueries?We can assume that the rows do have a unique ID.Unfortunately, it uses a self-join, so it's treatedas two tables. Thus, we can't just replace the keyword SELECT with DELETE, as you can't delete from "two tables".Or perhaps we can, with some trick from the guru.Ray" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-19 : 09:20:47
|
quote:
It will do it without subqueries. This provides the additional advantage of making it portable to other SQLs, like MySQL.
I can't remember who used to have this as their signature:"In theory there is no difference between theory and practice, but in practice there is!"Cross-platform SQL is a great theory and IMHO a bad practice, if it's possible at all. 99.999% of the time the SQL will not perform as well as a platform-specific version that accomplishes the same function. I can't think of a better example than the Snitz forum code (the forum that SQL Team uses). In their quest to create a cross-platform system a lot of the performance on SQL Server is compromised, sometimes tremendously. Most of this was done to accommodate MySQL and its peculiarities, and I think that Snitz probably runs best on MySQL. graz did an in-depth analysis on the performance issues here:http://www.sqlteam.com/item.asp?ItemID=6891Anyway, I digress. My point is that write-once, cross-platform SQL is NOT the best goal to set for yourself. You'll learn less about what each platform can do and restrict the maximum performance you can get out of each system. Additionally, you should also look to create your tables in such a way as to PREVENT duplicates from being inserted, instead of trying to clean them up afterwards. An ounce of prevention is worth a pound/ton of cure.Have you read this yet?http://www.sqlteam.com/item.asp?ItemID=3331 |
 |
|
|
|
|
|
|
|
deleting dupes without subqueries?