0

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

Community
  • 1
  • 1
Lucas Vasconcelos
  • 3
  • 1
  • 3
  • 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 Jul 11 '12 at 20:59

1 Answers1

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
juergen d
  • 201,996
  • 37
  • 293
  • 362