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 |
Cornelius19
Starting Member
30 Posts |
Posted - 2008-08-06 : 10:24:49
|
Hi,I have a loop that selects a record at a time, processes it and deletes it: select top 1 @v = word from tmp1... delete tmp1 where word = @vIt works fine as far as there are no duplicate records: exactly one record is selected, processed and deleted. However, if there are duplicates, it will select and process one record only but delete multiple records. In order to avoid that, I tried "delete top 1" (just like "select top 1") but got an error: Incorrect syntax near the keyword 'top'.What is the best way to match select and delete?Cornelius |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-08-06 : 10:53:36
|
What are you trying to do? If you can't exactly specify what record to delete what do you expect?Here's a hint - TOP anything without ORDER BY is meaningless. Do you mean the oldest/newest/biggest/smallest?To directly address your question - you have not specified what you want to be deleted when there are multiples,so How TF are we supposed to know? Try defining what you want a bit better then the SQL will become clearer. |
 |
|
Cornelius19
Starting Member
30 Posts |
Posted - 2008-08-06 : 11:14:03
|
In this case, the order is irrelevant: the loop needs to go through all records, process them one by one and delete them from the temporary table. I did not choose TOP 1 because of any ORDER BY criteria but simply to choose one record at a time: no matter which, in which order.Anyways, I can change the select part to “SELECT TOP 1 @v = word FROM tmp1 ORDER BY word” (i.e. alphabetical order): there will be no ambiguity about the selected record (not sure however how it handles duplicates) but it still does not help me to delete the very same unambiguous record.Cornelius |
 |
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2008-08-06 : 11:39:28
|
It can be tough when you don't have a primary key which is the case here. Since you are using a temp table, can you not add an identity field to it to loop on? |
 |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-08-06 : 11:48:24
|
[code]DECLARE @Sample TABLE ( Col1 VARCHAR(5), Col2 INT, Col3 CHAR(1) )INSERT @SampleSELECT 'ABC', 24, 'M' UNION ALLSELECT 'ABC', 24, 'M' UNION ALLSELECT 'DEF', 24, 'M' UNION ALLSELECT 'DEF', 24, 'F' UNION ALLSELECT 'GHI', 26, 'F' UNION ALLSELECT 'GHI', 26, 'F' UNION ALLSELECT 'GHI', 26, 'F' UNION ALLSELECT 'GHI', 26, 'F' UNION ALLSELECT 'GHI', 26, 'F' UNION ALLSELECT 'LMN', 27, 'M' UNION ALLSELECT 'LMN', 27, 'M' UNION ALLSELECT 'LMN', 27, 'M' UNION ALLSELECT 'PQRS', 25, 'F' UNION ALLSELECT 'XYZ', 24, 'M' UNION ALLSELECT 'XYZ', 25, 'M'SELECT *FROM @SampleINSERT @SampleSELECT Col1, Col2, CASE Col3 WHEN 'M' THEN 'A' ELSE 'B' ENDFROM @SampleGROUP BY Col1, Col2, CASE Col3 WHEN 'M' THEN 'A' ELSE 'B' ENDHAVING COUNT(*) > 1DELETEFROM @SampleWHERE Col3 IN ('M', 'F')UPDATE @SampleSET Col3 = CASE Col3 WHEN 'A' THEN 'M' ELSE 'F' ENDSELECT *FROM @Sample[/code] E 12°55'05.25"N 56°04'39.16" |
 |
|
rajendermakhija
Starting Member
2 Posts |
Posted - 2008-08-07 : 00:35:05
|
set rowcount 1After this use Delete coommad.Regards,Rajender |
 |
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2008-08-07 : 04:21:18
|
quote: Originally posted by rajendermakhija set rowcount 1After this use Delete coommad.Regards,Rajender
This is not sure to delete exactly that record which was selected before!Webfred |
 |
|
Cornelius19
Starting Member
30 Posts |
Posted - 2008-08-07 : 15:47:09
|
Thanks guys! I added an identity field to the temporary table: it should work fine now.Cornelius |
 |
|
|
|
|
|
|