Delete duplicate rows according to condition

Question

I am using PostgreSQL database. I have the data like below.

   id      name1              name2            modified_date
   - - - - -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
   40      Balfe           | Pat            | 2010-10-01 17:48:44.804823 
   41      Battigan        | David          |                            
   42      Balfe           | Pat            | 2010-10-01 12:46:49.627593 
   44      Balfe           | Pat            | 2010-10-04 14:04:28.72415  
   45      Balfe           | Pat            | 
   46      Balfe           | Pat            | 2010-10-01 17:30:39.193153 
   47      Balfe           | Pat            |

I want to delete some rows from the table. The conditions to delete are:

It will delete duplicate records with modified_date = Null
--> one copy of duplicate records should not be deleted.
Records with modified date should not be deleted although they are duplicate.

From the table above, the record with id 47 or 45 should be deleted.

Peter Lang · Accepted Answer · 2010-10-04 10:21:44Z

4

This one should delete all records with modified_date Is Null, where another record with the same name1 and name2 exists, which also has modified_date Is Null.

The record with the lowest id will not be deleted.

Delete From your_table t1
Where modified_date Is Null
And Exists ( Select 1
             From your_table t2
             Where t2.name1 = t1.name1
               And t2.name2 = t2.name2
               And t2.modified_date Is Null
               And t2.id < t1.id
           )

answered Oct 4, 2010 at 10:21

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thomas O · Accepted Answer · 2010-10-04 10:22:43Z

0

Hmm, something like:

DELETE FROM tablename 
WHERE id NOT IN(SELECT DISTINCT id 
                FROM tablename 
                WHERE modified_date = Null)

Not sure if PostgreSQL supports subqueries with an IN statement though.

answered Oct 4, 2010 at 10:22

Thomas O

6,29012 gold badges47 silver badges63 bronze badges

1 Comment

Peter Lang Over a year ago

Would have to be Is Null instead of = Null, but then it would delete all records where it is Null, without keeping one record...

Collectives™ on Stack Overflow

Delete duplicate rows according to condition

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related