How to delete data from multiple tables in mysql database?

Question

I have a problem with deleting data from my database. I have several tables connected by FK. I just thought it would be the same as inserting data. But it seems it's not the same.

Here is my code:

DELETE FROM uzivatel, inzerat_nabizim, lokalita_nabizim, pronajem_pokoj_nabizim, osoba_nabizim, typ_pokoje_nabizim, vybaveni_pokoj_nabizim, zarizeni_byt_nabizim
                     WHERE uzivatel.id_uzivatel = '$id_uzivatel'
                     AND inzerat_nabizim.id_uzivatel = uzivatel.id_uzivatel
                     AND pronajem_pokoj_nabizim.id_inzerat_nabizim = inzerat_nabizim.id_inzerat_nabizim
                     AND lokalita_nabizim.id_pronajem_pokoj_nabizim = pronajem_pokoj_nabizim.id_pronajem_pokoj_nabizim
                     AND osoba_nabizim.id_pronajem_pokoj_nabizim = pronajem_pokoj_nabizim.id_pronajem_pokoj_nabizim
                     AND typ_pokoje_nabizim.id_pronajem_pokoj_nabizim = pronajem_pokoj_nabizim.id_pronajem_pokoj_nabizim
                     AND vybaveni_pokoj_nabizim.id_pronajem_pokoj_nabizim = pronajem_pokoj_nabizim.id_pronajem_pokoj_nabizim
                     AND zarizeni_byt_nabizim.id_pronajem_pokoj_nabizim = pronajem_pokoj_nabizim.id_pronajem_pokoj_nabizim
                     AND lokalita_nabizim.id_pronajem_pokoj_nabizim = pronajem_pokoj_nabizim.id_pronajem_pokoj_nabizim;

Any help will be highly appreciated.

If there's something not clear, ask me.

Alias names are great for readability. Could you use ON DELETE CASCADE? — VMai
– VMai, Commented Jul 12, 2014 at 15:18

Álvaro González · Accepted Answer · 2014-07-12 15:18:47Z

2

These are the possibilities listed in the manual:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

You are using DELETE FROM ... WHERE, which is not listed.

answered Jul 12, 2014 at 15:18

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3815206 Over a year ago

I'm sorry, but I don't get this. DELETE FROM ... WHERE is also possible, I saw examples of these so it's possible.

Álvaro González Over a year ago

It's probably easier to just tweak your syntax and adopt one of the valid ones (either add stuff between DELETE and FROM to get #1 or add a USING clause to get #2) than to start arguing with the MySQL parser.

Collectives™ on Stack Overflow

How to delete data from multiple tables in mysql database?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related