CODEWITHSUNDEEP
mysqlsql
techie2k
techie2k

Reputation: 559

Sql deleting orphan records with composite primary key

The table has a composite key with both source and target

source    target
 1        2
 2        3
 2        4
 3       -1
 4       -1

I wanted to delete records with the target value -1 and the source should not be in the target. In order to achieve the same, I'm using two different queries to delete the relevant records.

Delete relationships

Deleting a record(on the above-mentioned sample) with value 2 in source and target would result in an empty table.

Entries with -1 as target value and source value are not being referred in any other record then consider as orphan record and delete.

Is there a better way to delete it?

Upvotes: 0

Views: 169

Answers (1)

forpas
forpas

Reputation: 164164

This:

delete t
from tablename t
where
  t.target = -1
  and 
  t.source not in (
    select target from (
      select target from tablename t
    ) b
  )

deletes all rows with target = 1 unless source is included in any row in the target column.
See the demo.
For this sample data:

| source | target |
| ------ | ------ |
| 1      |  2     |
| 2      |  3     |
| 3      |  4     |
| 3      | -1     |
| 4      | -1     |
| 5      | -1     |

only the last row will be deleted.

Upvotes: 1

Related Questions