delete top 1 - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

delete top 1

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 = @v

It 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 @Sample
SELECT 'ABC', 24, 'M' UNION ALL
SELECT 'ABC', 24, 'M' UNION ALL
SELECT 'DEF', 24, 'M' UNION ALL
SELECT 'DEF', 24, 'F' UNION ALL
SELECT 'GHI', 26, 'F' UNION ALL
SELECT 'GHI', 26, 'F' UNION ALL
SELECT 'GHI', 26, 'F' UNION ALL
SELECT 'GHI', 26, 'F' UNION ALL
SELECT 'GHI', 26, 'F' UNION ALL
SELECT 'LMN', 27, 'M' UNION ALL
SELECT 'LMN', 27, 'M' UNION ALL
SELECT 'LMN', 27, 'M' UNION ALL
SELECT 'PQRS', 25, 'F' UNION ALL
SELECT 'XYZ', 24, 'M' UNION ALL
SELECT 'XYZ', 25, 'M'

SELECT *
FROM @Sample

INSERT @Sample
SELECT Col1,
Col2,
CASE Col3
WHEN 'M' THEN 'A'
ELSE 'B'
END
FROM @Sample
GROUP BY Col1,
Col2,
CASE Col3
WHEN 'M' THEN 'A'
ELSE 'B'
END
HAVING COUNT(*) > 1

DELETE
FROM @Sample
WHERE Col3 IN ('M', 'F')

UPDATE @Sample
SET Col3 = CASE Col3
WHEN 'A' THEN 'M'
ELSE 'F'
END

SELECT *
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 1
After this use Delete coommad.

Regards,

Rajender

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-07 : 04:21:18