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.

Look here: http://stackoverflow.com/questions/1233451/delete-from-two-tables-in-one-query — qqilihq, Jan 18 '14 at 19:18

score 0 · Answer 1 · answered Jan 18 '14 at 19:20

0

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};"

answered Jan 18 '14 at 19:20

user1918305

score 0 · Accepted Answer · answered Jan 18 '14 at 19:28

0

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};

answered Jan 18 '14 at 19:28

Gordon Linoff

Can you please update the query? . I dont have meta_key its just userid. but i got the idea. Thank you so much . – user3201500 Jan 18 '14 at 19:30
Thank you! it worked! i was just wondering why you use only JOIN not INNER JOIN? – user3201500 Jan 18 '14 at 19:34
1

@user3201500: JOIN = INNER JOIN in most, if not all, SQL products. JOIN is shorter while INNER JOIN results in fewer questions. – Andriy M Jan 18 '14 at 19:38
DELETE FROM user, user_meta from user INNER JOIN user_meta WHERE user.userid=user_meta.userid and user.userid=".$id.", i use this as query but it didnt work. Can you tell me why? – user3201500 Jan 18 '14 at 19:40
@user3201500 . . . Between the `delete` and the `from` are the tables needed for deletion. Note that this query starts `delete u, um from . . .`. – Gordon Linoff Jan 18 '14 at 19:42

2 Answers2