Using the "UNION" Delete MySQLI Query

Question

I want to delete some informations of my database in 2 tables.

First question, is it possible ? If yes:

I had tried a UNION DELETE query, but it doesn't work.

$delete = mysqli_query($sql, "(DELETE FROM table1 WHERE id LIKE '".$id."' && userid LIKE '".$userid."') UNION (DELETE FROM table2 WHERE source_id LIKE '".$id."' && userid LIKE '".$userid."')");

What do you intend to get by delete and union in one query? Why not using two separate delete statements? — Olesya Razuvayevskaya, Apr 24 '14 at 10:22
I want to delete informations of 2 tables, i thought this can be realised with Union. — asd, Apr 24 '14 at 10:24
I think you can JOIN and delete... something like `DELETE tbl1, tbl2 FROM table1 as tbl1 JOIN table2 as tbl2 ON.......... WHERE id.tbl1 LIKE '".$id."' ...........` — stackrocha, Apr 24 '14 at 10:29
No, simply, use two sequential delete statements. Also, I have just noticed &&. In mysql, you have to use AND operator instead — Olesya Razuvayevskaya, Apr 24 '14 at 10:30

score 5 · Accepted Answer · edited Dec 14 '14 at 23:42

5

UNION is used only with SELECT statements:

http://dev.mysql.com/doc/refman/5.7/en/union.html

If you don't want to use two separate DELETE statements and those two tables are related, try using the JOIN statement:

https://dev.mysql.com/doc/refman/5.7/en/join.html

edited Dec 14 '14 at 23:42

potashin

44,205
11
83
107

answered Apr 24 '14 at 10:34

jfranki

66
1

score 2 · Answer 2 · answered Apr 24 '14 at 10:27

2

Try this :

DELETE FROM 
    n.table1 INNER JOIN m.table2
WHERE 
    n.id = m.source 
  AND 
    n.userid = m.userid 
  AND  
    n.id LIKE '".$id."' 
  AND 
    n.userid LIKE '".$userid."'

answered Apr 24 '14 at 10:27

potashin

44,205
11
83
107

wow...do you even understand what `n.table1 INNER JOIN m.table2` is gonna do here?? :) – NoobEditor Apr 24 '14 at 10:32
@NoobEditor : I have not tested, but [it looks great while he is using mysql](http://stackoverflow.com/a/1233479/3444240) – potashin Apr 24 '14 at 10:34
even if it serves the purpose....i certainly would not recommend it...why go against the native usage of functions and features??? – NoobEditor Apr 24 '14 at 10:36
@NoobEditor : I use only separate `DELETE`s, but OP wants to do it in one query, so yes, I would't recommend it, but there is a way to do it. Can you agree with it?:) – potashin Apr 24 '14 at 10:39
no..i wont....one should not blindly answer...answers should help others improve logic and best practice...not only just getting the solution.... *damn, i sound like a university professor* !! :p – NoobEditor Apr 24 '14 at 10:42
@NoobEditor: [MySQL approves](http://dev.mysql.com/doc/refman/5.1/en/delete.html) – potashin Apr 24 '14 at 10:44
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/51339/discussion-between-noobeditor-and-notulysses) – NoobEditor Apr 24 '14 at 10:48

score 2 · Answer 3 · answered Apr 24 '14 at 10:31

2

UNION ( set operations ), JOINS all these are used to show data, i.e., used with SELECT, when multiple tables are to be used to fetch data.

to DELETE or UPDATE, please use separate queries!!

In your case

$delete = mysqli_query($sql, "(DELETE FROM table1 WHERE id LIKE '".$id."' && userid LIKE '".$userid."'));

if($delete)
{
   $delete2 = mysqli_query($sql, "(DELETE FROM table2 WHERE source_id LIKE '".$id."' && userid LIKE '".$userid."')");
}

answered Apr 24 '14 at 10:31

NoobEditor

15,563
19
81
112

Okay Thank you. I'll make it so. – asd Apr 24 '14 at 10:37
care to mark any of the answers accepted if it helped...??? helps denoting question as closed!! :) – NoobEditor Apr 24 '14 at 10:43

Using the "UNION" Delete MySQLI Query

3 Answers3