Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

I'm trying to delete orphan entries in a MySQL table.

I have 2 tables like this:

Table files:

| id | ....

------------

| 1  | ....

| 2  | ....

| 7  | ....

| 9  | ....

table blob:

| fileid | ....

------------

| 1  | ....

| 2  | ....

| 3  | ....

| 4  | ....

| 4  | ....

| 4  | ....

| 9  | ....

The fileid and id columns can be used to join the tables together.

I want to delete all rows in table blob where fileid cannot be found in the table files.id.

So using the example above that would delete rows: 3 & 4(s) in the blob table.

1 Answer

0 votes
by (40.7k points)

You can use LEFT JOIN/IS NULL like this:

DELETE b FROM BLOB b 

 LEFT JOIN FILES f ON f.id = b.fileid 

 WHERE f.id IS NULL

Use NOT EXISTS like this:

DELETE FROM BLOB 

WHERE NOT EXISTS(SELECT NULL

 FROM FILES f

 WHERE f.id = fileid)

Use NOT IN this way:

DELETE FROM BLOB

 WHERE fileid NOT IN (SELECT f.id 

 FROM FILES f)

Note: Whenever it's possible, perform DELETEs within the transaction (assuming supported - IE: Not on MyISAM) therefore, you can use rollback to revert changes in case of problems.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.5k questions

32.5k answers

500 comments

108k users

Browse Categories

...