DELETE first matching record 1

[russland]

Hi,

is there a way to delete only the first matching record of my DELETE/WHERE statement?

e.g.
DELETE Parameters
WHERE Parameter = 'SportsType'

the delete request should halt if the first record is deleted.
thanks

 

[r937]

you're saying there is more than one row that satisfies the WHERE clause?

if so, and you want to delete only one of them, you will have to specify which one, using something other than "first"

you see, "first" has meaning only in the context of a sequence, and the sequence can only be based on the values in some column, obviously some other column besides the Parameter column

r937.com | rudy.ca

 

[russland]

okay, cheers. I was hoping there's something like in SELECT.
e.g. SELECT TOP 1 ATTR FROM....

 

[JarlH]

ANSI SQL has no TOP functionality, neither in SELECT nor DELETE.

You'll have to use a cursor and do a DELETE WHERE CURRENT.

Something like
DECLARE c CURSOR FOR SELECT 1 FROM Parameters
WHERE Parameter = 'SportsType'
OPEN c;
FETCH c INTO :ival;
DELETE FROM Parameters WHERE CURRENT OF c;
CLOSE c;

 

[russland]

okay, this sh** works great. thanks again jarl.