How to delete a row from every connected table-CodePudding

CREATE TABLE X(
id INTEGER primary key
);

CREATE TABLE XconY(
id INTEGER not null references x(id),
id2 varchar(20) not null references Y(id2),
);

CREATE TABLE Y(
col BOOLEAN,
id2 varchar(20) PRIMARY KEY
);

And my goal is to delete a value from table X and it should delete the associated value from XconY and Y.

INSERT INTO X VALUES (1);
INSERT INTO XconY VALUES (1,"hello");
INSERT INTO Y VALUES (1,"hello");

DELETE FROM X WHERE id = 1;

After the following set of these instructions all 3 tables should be empty. Other than making individual queries is there a better way to do this?

CodePudding user response：

MySQL supports multi-table DELETE syntax (this is not standard SQL and may not be supported if you use other brands of database).

mysql> select * from X join XconY using (id) join Y using (id2);
 ------- ---- ------ 
| id2   | id | col  |
 ------- ---- ------ 
| hello |  1 |    1 |
 ------- ---- ------ 
1 row in set (0.00 sec)

mysql> delete X, XconY, Y from X join XconY using (id) join Y using (id2);
Query OK, 3 rows affected (0.01 sec)

mysql> select * from X join XconY using (id) join Y using (id2);
Empty set (0.00 sec)