SQL - Delete a single duplicate entry

Question

I am refreshing myself on SQL and that database I was working on glitched (online training database, not important) and duplicated 1 of my entries. It's just a firstname, lastname, age database.

I want to remove the duplicate entry - but can't just do a simple remove/where statement because it would delete both entries.

I know how to remove all the duplicates from a database using a temporary table and renaming it - but that seems like overkill and I suspect there is an easier way.

If you google "Delete Duplicate Rows (My Database Product)" you will probably find examples right here on SO. But without knowing your database product, it's hard to say which question yours is a duplicate of. — Tab Alleman, Mar 20 '15 at 16:30
I would think there was a way to do it that would work with any/all versions of SQL. Maybe I am wrong. — Anthony Nichols, Mar 20 '15 at 16:32
I agree this is a duplicate - I searched and was only coming up with ways to prune the DB, not remove a single entry. I will delete this questions shortly. Thanks for pointing me in the right direct. — Anthony Nichols, Mar 20 '15 at 16:41

score 0 · Accepted Answer · answered Mar 20 '15 at 16:31

0

Which RDBMS?

In most cases, you can do something along the lines of:

set rowcount 1
delete from tbl where firstname = 'john' and lastname = 'doe'

There are more complex options for pruning duplicates, but this should work for you.

answered Mar 20 '15 at 16:31

breno

16
1

1

This option is deprecated in sql server and should be avoided. – Sean Lange Mar 20 '15 at 16:34

score 0 · Answer 2 · answered Mar 20 '15 at 16:32

0

In SQL Server you could do it like this:

WITH MyCTE AS
(
    SELECT firstname, 
           lastname, 
           age, ROW_NUMBER() OVER (ORDER BY firstname, lastname, age) AS rn
    FROM TableName
)
DELETE FROM MyCTE WHERE rn > 1

answered Mar 20 '15 at 16:32

Giannis Paraskevopoulos

18,261
1
49
69

score 0 · Answer 3 · edited May 23 '17 at 12:06

0

If the database is an Oracle database you can use: rowid.

See the following link: Delete duplicate rows from Oracle tables.

For SQL server you can use the rowid also. See this thread on stackoverflow.

if the database is MySQL there is another thread on stackoverflow.

edited May 23 '17 at 12:06

Community

1
1

answered Mar 20 '15 at 17:02

Daniël Younis

1

SQL - Delete a single duplicate entry

3 Answers3