• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 614
  • Last Modified:

Delete the duplicate record, but based on the contents of two fields.

HI!
When only the contents of one field defines which record is duplicated, I know to create the phrase  SQL to delete.
But in this case, not.
Because, I need to delete the duplicate record, but based on the contents of two fields in the table.
When the two fields together, repeat content.

Example:
 Field1 and  Field2

2345|456|->keep
2346|358|->keep
2347|234|->keep
2347|675|->keep
2347|456|->keep
2347|456|------>delete
2347|456|------>delete
2347|432|->keep
2348|432|->keep

Can anyone help me by writing a sample in SQL?
Please use only standard SQL commands.
Because DB is to use the Firebird, and all DBs perform  standards commands.
Thank you very much.
0
adrex
Asked:
adrex
  • 2
1 Solution
 
Pratima PharandeCommented:
try this .. t is your tablename

TABLE tmp AS SELECT DISTINCT(t.Field1,t,Field2), * FROM t;
DROP TABLE t;
ALTER TABLE tmp RENAME TO t;

refer
http://stackoverflow.com/questions/1746213/how-to-delete-duplicate-entries-in-postgresql
0
 
Pratima PharandeCommented:
try this also

using single query

http://wiki.postgresql.org/wiki/Deleting_duplicates
0
 
adrexAuthor Commented:
pratima_mcs
From your example. I got to formulate a solution.
Same I was far more extensive than yours.
And your example uses command incompatible with the Firebird, but I got to make replacement by equivalents.
It worked.
Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now