asked on

sub query delete

I'm trying to get the below query working to remove rows where the sum count by player is 0.

What's wrong with my syntax?

delete from temp_Dismissals,
(
SELECT playername, sum(CountDismissals) as Summary
FROM `temp_Dismissals`
GROUP BY playername
) derived 
where derived.Summary = 0

Open in new window

Haroon Ur Rashid

Hi PeterErhard,
Please try this.
Regards,

delete from temp_Dismissals
where playername IN (
                     SELECT playername 
                     from temp_Dismissals 
                     GROUP BY playername 
                     having sum(CountDismissals) = 0
                    )

Open in new window

PeterErhard

ASKER

Thanks Haroon. I get this error though:

Error
 
SQL query: Documentation
 
DELETE FROM temp_Dismissals WHERE playername IN (
SELECT playername
FROM temp_Dismissals
GROUP BY playername
HAVING sum( CountDismissals ) =0
)
 
MySQL said: Documentation
#1093 - You can't specify target table 'temp_Dismissals' for update in FROM clause

Open in new window

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Haroon Ur Rashid

Oh, sorry i miss some basic things in this query. query can be executed when half of table already deleted
which is wrong. ( it is limitation of implementation) anyway i give u a work around, you need to dump record which sub query give you and then delete these record in main table.

Haroon Ur Rashid

Some thing like this.

insert into temp_temp (name)
SELECT playername
FROM temp_Dismissals
GROUP BY playername
HAVING sum( CountDismissals ) =0;
 
delete a
from temp_Dismissals a
inner join temp_temp b on a.playername = b.name;

Open in new window

PeterErhard

ASKER

Worked perfectly - thanks mwvisa, and thanks for the help too haroon.

Kevin Cross

Glad to help.
Happy coding!
--isa