Simply merge 2 Delete query to make it one

Question

I have 2 delete SQL query i want to make it one.

$sql1= DELETE FROM user WHERE userid={$id}

another is

$sql = Delete FROM user_meta where userid = {$id}

Any suggestions? I want to make it simple.

Answer 1

You can't delete from 2 tables in DELETE statement, but you issue 2 statements in 1 go:

$sql = "DELETE FROM user WHERE userid={$id};
DELETE FROM user_meta WHERE meta_key = {$id};"

Answer 2

The normal way of deleting things from different tables "at the same time" is to wrap them in a transaction. Something like:

begin transaction thedeletes
    DELETE FROM user WHERE userid={$id};
    DELETE FROM user_meta WHERE userid = {$id}
commit transaction thedeletes;

In MySQL you can actually put these in the same query:

delete u, um
    from user u join
         user_meta um
         on u.userid={$id} and um.userid = {$id};