How to Delete Orphan Rows in a Database Table Based on Unmatched IDs?

Delete orphans based on not matching ID

Question:

Delete

in the blob table cannot find a line of fileid. files id Table structure:

Expected results:

<code>files表：
| id | .... |
|---|---|
| 1  | .... |
| 2  | .... |
| 7  | .... |
| 9  | .... |

blob表：
| fileid | .... |
|---|---|
| 1  | .... |
| 2  | .... |
| 3  | .... |
| 4  | .... |
| 4  | .... |
| 4  | .... |
| 9  | .... |</code>

Delete The line of in the table is 3 and 4, because they have not matched the in the

table.

blob Solution: fileid files fileid <.> 1. Use left join/is null:

<.> 2. Use not exists:

<.> 3. Use not in:

<code class="language-sql">DELETE b FROM BLOB b 
  LEFT JOIN FILES f ON f.id = b.fileid 
      WHERE f.id IS NULL</code>

Warning:

<code class="language-sql">DELETE FROM BLOB 
 WHERE NOT EXISTS(SELECT NULL
                    FROM FILES f
                   WHERE f.id = fileid)</code>

To ensure that it can be rolled when there is an error, please perform the delete operation in the transaction.

The above is the detailed content of How to Delete Orphan Rows in a Database Table Based on Unmatched IDs?. For more information, please follow other related articles on the PHP Chinese website!