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.
Joined: 23 Nov 2006 Posts: 19270 Location: Inside the Matrix
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.