SQL statement to delete multiple fields in a table MYSQL

Question

I could finally solve the DELETE CASCADE. for these tables.

Now... I need delete all row on AlergiaTipo when these seems related to a alergiagrupo on the table ALERGIA. I make this statement.

delete from alergiatipo where ID in (select alergiatipo.ID from alergia,alergiatipo where
alergia.AlergiaTipo_ID = alergiatipo.iD and alergia.ID in (select alergia.ID from 
alergia,alergiagrupo where alergia.AlergiaGrupo_ID = alergiagrupo.ID AND 
alergiagrupo.ID = '2'));

but return me this error

Error Code: 1093. You can't specify target table 'alergiatipo' for update in FROM clause

Gordon Linoff · Accepted Answer · 2014-10-07 03:15:40Z

This is your delete with proper explicit join syntax:

delete from alergiatipo
    where ID in (select alergiatipo.ID
                 from alergia join
                      alergiatipo
                      on alergia.AlergiaTipo_ID = alergiatipo.iD
                 where alergia.ID in (select alergia.ID 
                                      from alergia join
                                           alergiagrupo 
                                           on alergia.AlergiaGrupo_ID = alergiagrupo.ID 
                                      where alergiagrupo.ID = '2'
                                     )
               );

In MySQL, you cannot specify the table being deleted elsewhere in an update or delete. Plus, your delete is rather overdone. I think you just want the following:

delete alt
    from alergiatipo alt join
         alergia a
         on a.AlergiaTipo_ID = alt.iD join
         alergiagrupo ag
         on a.AlergiaGrupo_ID = ag.ID
    where ag.ID = 2;

I also removed the single quotes around 2. I'm guessing the column is an integer, so you should use an integer constant rather than a string constant.

Collectives™ on Stack Overflow

SQL statement to delete multiple fields in a table MYSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related