Solved

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

Posted on 2013-01-21
3
598 Views
Last Modified: 2013-01-22
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
Comment
Question by:adrex
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 38804061
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 38804075
try this also

using single query

http://wiki.postgresql.org/wiki/Deleting_duplicates
0
 

Author Comment

by:adrex
ID: 38806780
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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
where can i get list of all countries names along with their cities name into Excel? 13 51
Can SQL do Pythagoras? 8 20
Postgresql 9.6 10 10
Postdresql 1 7
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question