There are three tables:

table1: id, name
table2: id, name, table1_id
table3: id, name, table2_id

How when deleting a field from the top, remove all the associated fields in the bottom? The problem of more nesting is not worth it, 3 tables are 100% maximum. I tried it like this, but it does not work correctly, it blows down the entire table3

DELETE table1 , table2 , table3 FROM table1 INNER JOIN table2 ON table2.table1_id = table1.id INNER JOIN table3 ON table3.table2_id = table2.id WHERE table1.id = 1

  • one
    FK is there? ON DELETE CASCADE. Not? create. - Akina
  • one
    As Akina has correctly noted, use cascade deletion - user218976
  • demolishes table3 entirely See , data such ... fiddle . - Akina

2 answers 2

Option 1 (recommended): Configure cascading deletion for foreign keys table1_id and table2_id .

Option 2: Alternately delete the records first from table3 , then table2 and finally from table1

 DELETE FROM table3 WHERE table2_id in (SELECT id FROM table2 WHERE table1_id in (SELECT id FROM table1 WHERE [здесь ваше условие для выбора удаляемой записи из таблицы 1])) DELETE FROM table2 WHERE table1_id in (SELECT id FROM table1 WHERE [здесь ваше условие для выбора удаляемой записи из таблицы 1]) DELETE FROM table1 WHERE [здесь ваше условие для выбора удаляемой записи из таблицы 1]

    You can do this:

    • Add constrains

      ALTER TABLE table2 ADD CONSTRAINT FK_table1_table2 FOREIGN KEY (table1_id) REFERENCES table1 (id) ON DELETE CASCADE ON UPDATE CASCADE;

      ALTER TABLE table3 ADD CONSTRAINT FK_table2_table3 FOREIGN KEY (table2_id) REFERENCES table2 (id) ON DELETE CASCADE ON UPDATE CASCADE;

    • Make removal

      DELETE FROM table1 t1 WHERE t1.id = 5;