How to implement delete Intermediate table

Question

In my Java program, I want to achieve such a piece of SQL, when delete table A with code using for condition,and delete Table B(linkId=deleted ids of Table A)

Table A:

===============================================
id          |        code      |    names     |
===============================================
1           |          A       |    name1     |
2           |          A       |    name2     |
3           |          A       |    name3     |
4           |          B       |    name4     |
5           |          B       |    name5     |
6           |          B       |    name6     |
7           |          B       |    name7     |
8           |          C       |    name8     |

Table B( Intermediate table):

================================
id          |       linkId     |...
================================
1           |          1       |...
2           |          2       |...
3           |          2       |...
4           |          2       |...
5           |          3       |...
6           |          7       |...
7           |          8       |...
8           |          8       |...

like this:

delete from A where code = A
delete from B where linkId =(1,2,3)

How to use an sql statement to achieve?

Answer 1

If you can not add a FOREIGN KEY constraint with the ON DELETE CASCADE option, just change the order of deletion.

-- Delete from TableB (Remove all related records from the child table)
DELETE FROM
  TableB
WHERE
 EXISTS (SELECT 1 FROM TableA WHERE TableB.LinkId = TableA.id AND TableA.code = 'A');

-- Delete from TableA (Remove the record from the parent table)
DELETE FROM TableA WHERE code = 'A';

Consider to do all actions related to this multi-table deletinon in a transaction to prevent data loss when one of the statements fails.

Transactions in MySQL

Transactions in Microsoft SQL Server (MSSQL)

Transactions in PostgreSQL

Answer 2

If there can't be lines in B with ids not existing in a you can write

delete from B where not exists ( select 1 from A where A.id=B.linkId);

Otherwise you should perform the delete in B before deleting from A to make sure the deleted rows are not definitiley lost.