CODEWITHSUNDEEP
mysqlsqlsubquerysql-deletemysql-error-1093
Febz
Febz

Reputation: 39

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

Upvotes: 2

Views: 1333

Answers (2)

Deepak Rai
Deepak Rai

Reputation: 2203

MySQL does not allow a table that is being updated/deleted in the outer query to be selected from in any sub-query of the statement.

Here you are deleting from concorre table and also referring the same table in sub-query.

You can try something like this : MySQL DELETE FROM with subquery as condition

Upvotes: 0

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7242

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

Upvotes: 0

Related Questions