How to delete an entire row of data in multiple tables with a certain ID in MySQL?

Question

For example, I have four tables now. The columns of each table are different, but they all have an ID column. In some tables, the ID is not used as the primary key. I now need to set the ID value in the four tables based on the ID value. If all the data in row 12 is deleted, how should I write the DELETE statement? Please give me some advice

#

Answer 1

delete t1,t2,t3,t4 from t1 left join t2 on t1.id=t2.id left join t3 on t1.id=t3.id left join t4  on t1.id=t4.id where t1.id=12

Answer 2

For your problem, just execute four SQL statements.
You cannot guarantee that every table has the records you want to delete. There may be problems no matter how you join

Answer 3

START TRANSACTION;
    DELETE FROM t1 WHERE id=12;
    DELETE FROM t2 WHERE id=12;
    DELETE FROM t3 WHERE id=12;
    DELETE FROM t4 WHERE id=12;
COMMIT;

# ROLLBACK;  # 如果commit有错，请回滚．