Solved

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

Posted on 2013-01-21
3
590 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Data quality checks 2 49
SQL Database connect change in Access 2015 5 49
Remote Server returned '400 4.4.7 Message delayed' 3 64
update joined tables 2 27
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
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…
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…

919 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now