MySQL DELETE statement with an user variable on a where-in clause?

Question

Suppose I have this table:

drop table if exists t_user;
CREATE TABLE t_user (usr int, grp int);
INSERT INTO t_user VALUES (123456, 5);
INSERT INTO t_user VALUES (111111, 5);
INSERT INTO t_user VALUES (111311, 5);
INSERT INTO t_user VALUES (122111, 5);
INSERT INTO t_user VALUES (111111, 5);
INSERT INTO t_user VALUES (111211, 5);
INSERT INTO t_user VALUES (113211, 5);
INSERT INTO t_user VALUES (115311, 5);
INSERT INTO t_user VALUES (122253, 3);
INSERT INTO t_user VALUES (222331, 6);
INSERT INTO t_user VALUES (118811, 3);
INSERT INTO t_user VALUES (112111, 6);
INSERT INTO t_user VALUES (213111, 6);
INSERT INTO t_user VALUES (343321, 7);
INSERT INTO t_user VALUES (114431, 2);
INSERT INTO t_user VALUES (115111, 8);
INSERT INTO t_user VALUES (324353, 3);

I want to run a query like this:

SELECT distinct @u := usr FROM t_user WHERE grp < 6;

To hold the ids of a bunch of users and use the result on a delete statement that will delete all those users:

delete from t_user where usr in ( @u );

The problem is that each time i run these two statements the first listed user is deleted and not all of them as I wanted.

EDIT

I can't simply run everything on a single query. I really want to know how to do it separately.

EDIT²

I followed @juergen instructions and tried using Temporary tables:

CREATE TEMPORARY TABLE t_temp (id int);

INSERT INTO t_temp SELECT DISTINCT usr FROM t_user WHERE grp < 6;

DELETE FROM t_user WHERE usr IN ( SELECT * FROM t_temp );

DROP TABLE t_temp;

I searched and found some similar answers, but i don't want to build a string and use it on my delete statement. I want something simpler.

How to store the results from a query into a temporary table: - Create table variable in MySQL

I am usually able to work around the SQL Error (1093): by wrapping the offending query in an inline view, that is, wrapping my subquery q (which references the table) inside another select, SELECT * FROM ( q ). The net effect is that MySQL is creating a temporary table just for the duration of the statement. — spencer7593
– spencer7593, Commented Jul 11, 2012 at 20:59

juergen d · Accepted Answer · 2012-07-11 20:20:11Z

3

just do it in one query

delete from t_user 
where usr in (SELECT distinct usr FROM t_user WHERE grp < 6)

or even shorter

delete from t_user WHERE grp < 6

Edit

A variable can only hold one entry. You could use a temp table to store the data you want.

Edit 2

You could try a join instead of subquery. Haven't tried it though:

delete from t_user u1
inner join t_user u2 on u1.usr = u2.usr 
WHERE u2.grp < 6

edited Jul 11, 2012 at 20:20

answered Jul 11, 2012 at 19:56

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lucas Vasconcelos Over a year ago

I'm sorry, this is just an example.. if I try to do everything on a single query on my real problem I get this error: SQL Error (1093): You can't specify target table 'my_table' for update in FROM clause.

Lucas Vasconcelos Over a year ago

Anyway, I will try to use a temp table! Thanks

juergen d Over a year ago

You could try a join instead of subquery. Maybe that works. See my update.

Lucas Vasconcelos Over a year ago

Actually i did the temp table thing and it solved my problem! Thank you!! I will update my question and put the solution!

Collectives™ on Stack Overflow

MySQL DELETE statement with an user variable on a where-in clause?

1 Answer 1

Edit

Edit 2

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Edit

Edit 2

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related