CODEWITHSUNDEEP
mysqldatabasesql-delete
Kelly
Kelly

Reputation: 21

MySQL Deleting based upon a query result

I have the following mysql query which finds the most recently modified and unique spawnpoint_id from a pokemon table in my database: 

SELECT 
    t1.spawnpoint_id, t1.last_modified
FROM
    pokemon t1
        INNER JOIN
    (SELECT 
        MAX(last_modified) last_modified, spawnpoint_id
    FROM
        pokemon
    GROUP BY spawnpoint_id) t2 ON 
    t1.spawnpoint_id = t2.spawnpoint_id
    AND t1.last_modified = t2.last_modified;

I get the results I want with the above.... but now, I want to delete all records that don't match these results.

I have tried to enclose the query in a DELETE .. NOT IN something like this:

DELETE FROM pokemon WHERE (spawnpoint_id, last_modified) NOT IN (
SELECT 
    t1.spawnpoint_id, t1.last_modified
FROM
    pokemon t1
        INNER JOIN
    (SELECT 
        MAX(last_modified) last_modified, spawnpoint_id
    FROM
        pokemon
    GROUP BY spawnpoint_id) t2 ON 
    t1.spawnpoint_id = t2.spawnpoint_id
    AND t1.last_modified = t2.last_modified) x;

but I'm getting MySQL syntax error. I've been searching for a couple of hours, and finally hoped someone here might be able to help me discover what I'm doing wrong. Many thanks.

EDIT: SHOW CREATE TABLE pokemon;

CREATE TABLE pokemon ( encounter_id varchar(50) NOT NULL, spawnpoint_id varchar(255) NOT NULL, pokemon_id int(11) NOT NULL, latitude double NOT NULL, longitude double NOT NULL, disappear_time datetime NOT NULL, individual_attack int(11) DEFAULT NULL, individual_defense int(11) DEFAULT NULL, individual_stamina int(11) DEFAULT NULL, move_1 int(11) DEFAULT NULL, move_2 int(11) DEFAULT NULL, last_modified datetime DEFAULT NULL, time_detail int(11) NOT NULL, PRIMARY KEY (encounter_id), KEY pokemon_spawnpoint_id (spawnpoint_id), KEY pokemon_pokemon_id (pokemon_id), KEY pokemon_disappear_time (disappear_time), KEY pokemon_last_modified (last_modified), KEY pokemon_time_detail (time_detail), KEY pokemon_latitude_longitude (latitude,longitude) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Upvotes: 1

Views: 69

Answers (1)

Stephan Lechner
Stephan Lechner

Reputation: 35154

I think the problem is that you use table pokemon, from which you want to delete rows, in the from-part of a subquery (which is not permitted).

One could get around this by first doing an update-statement that marks the rows to be deleted, and then do a separate delete statement. Note that the "must not use in the from-part"-restriction also applies to update-statements. Yet this can be solved by using a join rather than a subselect as follows:

create table a (
  x int,
  y int
);

insert into a (x,y) values (1,2),(3,4);

update a a1, (select max(a2.x) as x from a a2) a3 set a1.y = 0 where a1.x = a3.x;

delete from a where y=0

Upvotes: 1

Related Questions