CODEWITHSUNDEEP
mysqlsubquerysql-delete
ოთო შავაძე
ოთო შავაძე

Reputation: 1045

Deleting from table using subquery

I have for example this table "autos"

id | name
--------------
2  | Mercedes
5  | Mercedes
6  | Mercedes
7  | BMW 
9  | BMW

I want delete from this table all rows, except rows, where id is maximal for each auto. that is, I want after deleting, in table remains only this rows:

6 | Mercedes
9 | BMW

I write this query, but this not working (and not returns errors also).

DELETE FROM autos WHERE id NOT IN (
    SELECT id FROM (
        SELECT MAX(id) FROM autos GROUP BY name
    ) AS t
)

please tell me, how to make this?

Upvotes: 1

Views: 138

Answers (2)

Martin
Martin

Reputation: 6697

CREATE TABLE autos (`id` int AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(30));


INSERT INTO autos(name) VALUES ('Mercedes');
INSERT INTO autos(name) VALUES ('Mercedes');
INSERT INTO autos(name) VALUES ('Mercedes');

INSERT INTO autos(name) VALUES ('BMW');
INSERT INTO autos(name) VALUES ('BMW');
INSERT INTO autos(name) VALUES ('BMW');

DELETE FROM autos 
WHERE id NOT IN (
   SELECT id FROM ( 
       SELECT max(id) id FROM autos a GROUP BY a.name 
   ) a
);

Query OK, 4 rows affected (0.14 sec)

Hope this helps!

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 692231

AFAIK, the following should work:

DELETE FROM autos WHERE id NOT IN (
    SELECT max_id FROM (
        SELECT MAX(id) as max_id, name FROM autos GROUP BY name
    ) as t
)

Upvotes: 2

Related Questions