Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-21
3
Medium Priority
?
608 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 1500 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

886 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