Delete without alias

Question

I like to delete duplicates from a table game. There are entries with the same playerId and timeParameter and the ones with lowest gameId should remain.

I can query the entries with:

select a.`gameId` from `game` a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`playerId`, b.`timeParameter`
    having m = a.`gameId`);

But I can't use the alis a in the delete statement:

delete from `game` a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`playerId`, b.`timeParameter`
    having m = a.`gameId`);

Getting a syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MariaDB server version for the right syntax to use near 'a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`' at line 1

This error tells me nothing, but I know that I can't use aliases in a delete statement.

Is there a solution for this?

even you can not delete this by using select statement with same table — krishn Patel, Feb 03 '17 at 09:50
Have a look at this http://stackoverflow.com/questions/4562787/how-to-delete-from-select-in-mysql — rpd, Feb 03 '17 at 10:21
@vertex if you like my ans than can you up vote and accept it ? — krishn Patel, Feb 03 '17 at 10:26

sumit · Accepted Answer · 2017-02-03T10:12:26.990

1

You can make a sub query and make one derived table

delete from game where gameId IN
(
select gameId from(
select a.`gameId` from `game` a
where not exists
  (select min(b.`gameId`) as m from `game` b
    group by b.`playerId`, b.`timeParameter`
    having m = a.`gameId`)
)tmp
)

edited Feb 03 '17 at 10:12

answered Feb 03 '17 at 09:51

sumit

15,003
12
69
110

Thanks, but it doesn't work: `ERROR 1093 (HY000): Table 'game' is specified twice, both as a target for 'DELETE' and as a separate source for data` as krishn patel said. – Vertex Feb 03 '17 at 09:55
can you provide fiddle with sample data, I will give a try – sumit Feb 03 '17 at 09:57
It seems working. After deleting the same number of games remains as using the solution with the temporary table. I accept your answer because it uses only one statement. – Vertex Feb 03 '17 at 10:36

Rahul · Answer 2 · 2017-02-03T10:08:06.587

1

That's correct and per MySQL documentation if you are using alias then you will have to refer it in your DELETE statement. So either remove the alias a or make your DELETE statement like

delete a from `game` a ...

Quoting from Documentation

Note

If you declare an alias for a table, you must use the alias when referring to the table:

DELETE t1 FROM test AS t1, test2 WHERE ...

edited Feb 03 '17 at 10:08

answered Feb 03 '17 at 10:01

Rahul

76,197
13
71
125

score 1 · Answer 3 · answered Feb 03 '17 at 10:14

1

Using @krishn patel idea to create a temporary table this should work.

First create a table dontDeleteMe which contains all gameIds that should remain.

create table `dontDeleteMe` as
      select min(`gameId`) as `theId` from `game`
      group by `playerId`, `timeParameter`;

Than I can use this table for a subquery:

delete from `game`
where `gameId` not in
  (select `theId` from `dontDeleteMe`);

And after that I can drop the temporary table:

drop table `dontDeleteMe`;

answered Feb 03 '17 at 10:14

Vertex

2,682
3
29
43

can you try @rahul ans ? – krishn Patel Feb 03 '17 at 10:17
you can make derived table and do it using single sql..this is bad idea – sumit Feb 03 '17 at 10:19

Delete without alias

3 Answers3