Search code examples
mysqlsqlsubquerysql-deletemysql-error-1093

Cannot delete from same table in subquery from

I know this argument has been addressed in this site many times, I just can't find a working solution, so I thought i would post my specific case to you, maybe you can help me...

I have a festival from which I need to eliminate the songs that got less than the 20% of the average evaluation of the night before.

I tried with this: 

DELETE FROM concorre AS C 
WHERE C.dataSer='2014-02-24' 
AND C.votoTot<(SELECT AVG(B.votoTot) 
               FROM concorre AS B 
               WHERE B.dataSer='2014-02-23')/5

It resolves the "same table problem" but still gives me an error:

#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 'AS C WHERE C.dataSer='2014-02-24' AND C.votoTot<(SELECT AVG(votoTot) FROM concor' at line 1

Solution

  • Try this

    DELETE c FROM concorre C 
JOIN (SELECT AVG(B.votoTot) average FROM concorre B WHERE B.dataSer='2014-02-23') d
WHERE C.dataSer='2014-02-24' 
AND C.votoTot < d.average /5