Delete from two tables in one query

Question

I have two tables in MySQL    messages table     messageid messagetitle        usersmessages table  usersmessageid  messageid userid       Now if I want to delete from messages table it s ok  But when I delete message by messageid the record still exists on usersmessage and I have to delete from this two tables at once    I used the following query     DELETE FROM messages LEFT JOIN usersmessages USING messageid  WHERE messageid  1       Then I test      DELETE FROM messages   usersmessages     WHERE messages messageid   usersmessages messageid     and messageid  1       But these two queries are not accomplishing this task

User · Answer

Try this    DELETE a    b     FROM table1 as a  table2 as b   WHERE a id  Your value here  and b id  Your value here    I let id as a sample column   Glad this helps

User · Answer

The OP is just missing the table aliases after the delete  DELETE t1  t2  FROM table1 t1 LEFT JOIN table2 t2 ON t1 id   t2 id  WHERE t1 id   some id

User · Answer

You have two options   First  do two statements inside a transaction   BEGIN    DELETE FROM messages WHERE messageid   1    DELETE FROM usermessages WHERE messageid   1  COMMIT    Or  you could have ON DELETE CASCADE set up with a foreign key   This is the better approach   CREATE TABLE parent     id INT NOT NULL      PRIMARY KEY  id      CREATE TABLE child     id INT  parent id INT    FOREIGN KEY  parent id  REFERENCES parent id  ON DELETE CASCADE      You can read more about ON DELETE CASCADE here

User · Answer

You can also use like this  to delete particular value when both the columns having 2 or many of same column name   DELETE project   create test  FROM project INNER JOIN create test WHERE project project name  Trail  and  create test project name   Trail  and project uid  create test uid    1

User · Answer

You should either create a FOREIGN KEY with ON DELETE CASCADE   ALTER TABLE usersmessages ADD CONSTRAINT fk usermessages messageid FOREIGN KEY  messageid  REFERENCES messages  messageid  ON DELETE CASCADE     or do it using two queries in a transaction   START TRANSACTION    DELETE FROM    usermessages WHERE   messageid   1  DELETE FROM    messages WHERE   messageid   1   COMMIT    Transaction affects only InnoDB tables  though

User · Answer

DELETE a    b    FROM messages a  LEFT JOIN usersmessages b  ON b messageid   a messageid  WHERE a messageid   1   translation  delete from table messages where messageid  1  if table uersmessages has messageid   messageid of table messages  delete that row of uersmessages table

User · Answer

DELETE message    usersmessage   from users  usersmessage WHERE message messageid usersmessage messageid AND message messageid  1

User · Answer

Can t you just separate them by a semicolon   Delete from messages where messageid    1   Delete from usersmessages where messageid    1    OR  Just use INNER JOIN as below  DELETE messages   usersmessages  FROM messages  INNER JOIN usersmessages   WHERE messages messageid  usersmessages messageid and messages messageid    1

User · Answer

Try this please   DELETE FROM messages usersmessages  USING messages  INNER JOIN usermessages on  messages messageid   usersmessages messageid   WHERE messages messsageid  1

User · Answer

no need for JOINS      DELETE m  um FROM messages m  usersmessages um  WHERE m messageid   1   AND m messageid   um messageid

User · Answer

there s another way which is not mentioned here  I didn t fully test it s performance yet   you could set array for all tables -  rows you want to delete as below     set your tables array  array     table1    table2    table3         loop through each table for  i   0   i  lt  count  array    i          get each single array   single array    array  i        build your query   query    DELETE FROM  single array WHERE id    id         prepare the query and get the connection   data   con  GetCon  - gt prepare  query        execute the action   data- gt execute        then you could redirect the user to the home page   header  LOCATION      home page     hope this will help someone     Thanks

[sql] Delete from two tables in one query

Examples related to sql

Examples related to mysql