Query for deletion of first record from a table

ganna_f16 · New User Joined: 07 Mar 2005 Posts: 17 Location: India

Hi,
I am having a table with more duplicates(for example Empno). If I run the query like
DELETE FROM EMP-TABLE where EMP-NO='007';

This is going to delete all the rows which satisfies the condition.
But my requirement is to delete only the first record i.e if the condition satisfies for three records the it should delete only first record and remaining two records should be in table.
Thanks in advance.

Regards,
Ganna.

dbzTHEdinosauer · Posted: Wed Aug 15, 2007 3:28 pm

use a cursor for update. but which is the first?

ruodeer · New User Joined: 06 Jul 2007 Posts: 58 Location: home

yep, a method is that using a cursor to save the first coming one , then deleting the remaining.....

dick scherrer · Posted: Sat Aug 18, 2007 6:51 am

Hello,

Reading the responses to your question shows that different people have a different understanding of your requirement.

To continue with your example (3 rows containing emp-no=007), do you really want to delete the first one encountered and keep the other 2? Seems like this would result in the table still having duplicates.

Should there not be some ordering criteria (i.e. keep the oldest or keep the newest)? Deleting an arbitrary row that happens to meet the 007 criteria seems to be less than specific.

If you more clearly define your requirement, you may get better replies.