Deleting 1 and only 1 row from database 
Author Message
 Deleting 1 and only 1 row from database

Quote:

>    I have a database that has no primary key defined. The database allows
>dulplicate records. I want to remove some of the duplicate records so I
>thought of using "delete", but looking at the syntax, it seems the delete
>command will delete every matches, which is different from my goal -
>only the first matched record.

>    Can anyone show me the syntax for deleting only 1 record (say, the
>first match)? Thanks!

Use the pseudocolumn ROWID to pick the one you want to delete.
Say,

delete from foo
  where rowid in (select min(rowid) from foo where bar = 'baz');



Sat, 14 Apr 2001 03:00:00 GMT
 Deleting 1 and only 1 row from database

Hello,

If it doesn't matter which one you delete, you can use ROWNUM:

delete from your_table
where column1 = 'a_value'
and column2 = 'another_value'
and rownum = 1  -- or, if you want to delete two : rownum <= 2
/

Marc

Quote:

> Hi,

>     I am a novice to Oracle and SQL and I would like to ask you experts
> for help.

>     I have a database that has no primary key defined. The database allows
> dulplicate records. I want to remove some of the duplicate records so I
> thought of using "delete", but looking at the syntax, it seems the delete
> command will delete every matches, which is different from my goal -
> only the first matched record.

>     Can anyone show me the syntax for deleting only 1 record (say, the
> first match)? Thanks!

> Stannon



Sun, 15 Apr 2001 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Deleting 1 and only 1 row from database

2. Error: Row handle referred to a deleted row or a row marked for deletion

3. Row handle referred to a deleted row or a row marked for deletion

4. Another Duplicate Rows, Delete All But One Row

5. Delete trigger not updating rows using delete from

6. SQL 2000: Running DELETE under COM+ only deletes 500 rows

7. SQL Replication of selective rows deletes nonmatch rows in destination

8. SQL 2000: Running DELETE under COM+ only deletes 500 rows

9. delete only rows that can be deleted

10. delete data without deleting rows

11. Trigger: delete dependent records when deleting a row

12. Row Handle referred to a deleted row ... - Error


 
Powered by phpBB® Forum Software