Delete record in multiple tables in Mysql

Asked

Viewed 275 times

-1

I have several tables with the same field versao. I would like to delete simultaneously in all tables all records containing the field versao with the same 'XYZ' value'.

I tried to

DELETE FROM `t1`, `t2`, `t3` WHERE `versao` = 'XYZ';

but it did not happen...

What am I doing wrong? From now on, thank you very much.

mysql

  • Tried to make three separate commands?

  • Using https://dba.stackexchange.com/questions/4984/clear-all-tables-with-one-deletemetadata

  • all I don’t want is to do more than one command.

  • If you do not want to do several Deletes you can use forekeys and in the after delete Voce uses triggers and deletes the related tables, once done in the database you can use any front end that the bank will do the same

1 answer

2

The official syntax is:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

In your case after FROM you put more than one table. A solution would be:

DELETE FROM `t1` WHERE `versao` = 'XYZ';
DELETE FROM `t2` WHERE `versao` = 'XYZ';
DELETE FROM `t3` WHERE `versao` = 'XYZ';

  • Yes, yes. But I found cases erasing in more than one table... Using INNER JOIN. But they were even more complex situations involving relationship, which is not my case. My case is simpler (I think)...

  • That’s exactly what I want to avoid.

  • check this out : https://stackoverflow.com/questions/8598791/sql-delete-with-inner-join

  • If you do not want to do several Deletes you can use forekeys and in the after delete Voce uses triggers and deletes the related tables, once done in the database you can use any front end that the bank will do the same

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.