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
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