SQL: Delete by result of Select with Join

Question

I have the following mySQL Query which returns a list of ID's. I want to delete the rows with each ID of the list from the table "serverking_user"

SELECT serverking_user.id FROM (serverking_user
INNER JOIN serverking_logins
ON serverking_user.id=serverking_logins.uid)
WHERE serverking_logins.time < 3600
GROUP BY serverking_user.id
HAVING COUNT(serverking_logins.login) = 1

One possibility would be to loop through each ID by using "in". But since "in" is deactivated on my Sql Server I need to solve this with a "join".

Any ideas ?

Brian Driscoll · Accepted Answer · 2014-02-12 16:31:20Z

2

It has been a while since I have done MySQL development, but if I remember correctly this should work for you:

DELETE su
FROM serverking_user su
INNER JOIN serverking_logins sl
ON su.id=sl.uid
WHERE sl.time < 3600
GROUP BY su.id
HAVING COUNT(sl.login) = 1

In general, if you want to delete records from a single table in a JOIN, you just specify that table (or its alias) after DELETE

UPDATED QUERY
It seems that MySQL isn't fond of deleting where a grouped query is involved, so another possibility is to use an anti-join. However, I'm sure that the performance characteristics of this query will be suboptimal. It may be better to use two separate queries instead. Nonetheless, here is a solution using anti-joins:

DELETE su
FROM serverking_user su
INNER JOIN serverking_logins sl
ON su.id=sl.uid
LEFT JOIN serverking_logins antisl
ON sl.uid = antisl.uid
AND sl.id <> antisl.id
WHERE sl.time < 3600
AND antisl.id IS NULL

edited Feb 12, 2014 at 16:31

answered Feb 12, 2014 at 16:07

Brian Driscoll

19.7k3 gold badges51 silver badges66 bronze badges

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

4 Comments

jeff Over a year ago

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY su.id HAVING COUNT(sl.login) = 1' at line 6

Brian Driscoll Over a year ago

You could use an anti-join but I'm not sure if that will perform better than two separate queries as described in @Luaan's answer below.

jeff Over a year ago

and how would that look like ?

Brian Driscoll Over a year ago

@user1474444 I have updated my answer with another query. My assumption is that serverking_logins has a primary key field called id.

Luaan · Accepted Answer · 2014-02-12 16:07:44Z

1

Perhaps you could store the results of the select inside of a table variable and then join that?

declare @Ids table
(
  Id int
);

insert into @Ids (Id)
SELECT serverking_user.id FROM (serverking_user
INNER JOIN serverking_logins
ON serverking_user.id=serverking_logins.uid)
WHERE serverking_logins.time < 3600
GROUP BY serverking_user.id
HAVING COUNT(serverking_logins.login) = 1;

delete yt from YourTable yt
inner join @Ids Ids on Ids.Id = yt.id;

Note that the consistency of this depends on your transaction configuration, since you're doing two separate queries here.

answered Feb 12, 2014 at 16:07

Luaan

64.1k7 gold badges108 silver badges126 bronze badges

3 Comments

Luaan Over a year ago

@user1474444 Oh, I've only noticed now you're asking for a MySQL solution. Is this really so slow?

jeff Over a year ago

I don't know how slow that will be, but it looks a bit complicated for me since i am not really good in sql...

Luaan Over a year ago

@user1474444 Yeah, that's often a very bad metric. Try running it (without the delete, if you need so) and measure. Also, I'm not sure if MySql has table variables, you might have to use a temporary table, but it shouldn't be a big dfference.

Sev09 · Accepted Answer · 2014-02-12 16:11:25Z

1

DELETE FROM serverking_user
WHERE EXISTS
(SELECT *
FROM (serverking_user
INNER JOIN serverking_logins
ON serverking_user.id=serverking_logins.uid)
WHERE serverking_logins.time < 3600
GROUP BY serverking_user.id
HAVING COUNT(serverking_logins.login) = 1) a

edited Feb 12, 2014 at 16:11

answered Feb 12, 2014 at 16:06

Sev09

8732 gold badges13 silver badges27 bronze badges

4 Comments

jeff Over a year ago

#1093 - You can't specify target table 'serverking_logins' for update in FROM clause

Sev09 Over a year ago

Sorry, I had a typo that didn't match your table names. Try again, double checking that the table names are accurate.

jeff Over a year ago

thx, but still: #1093 - You can't specify target table 'serverking_user' for update in FROM clause

Sev09 Over a year ago

@user1474444, This is my fault. The query provided will work in SQL Server, but not MySQL. MySQL does not allow updates or deletes on the same table as what's in the WHERE clause. Please use Luaan's solution for a MySQL workaround.

Collectives™ on Stack Overflow

SQL: Delete by result of Select with Join

3 Answers 3

4 Comments

3 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related