I have a feedback table which contains user feedbacks and ratings as per some entity. There are cases in which multiple users have voted on the same entity which is not desirable and have entered the system due to a glitch.
The table schema is somewhat like this:
qa_id,int(10) //id of the entity
score,smallint(1)
user_id,int(3)
feed_time,datetime
I was trying to delete one of the duplicate entries (fortunately there is only one extra feedback) with this query:
delete from feedback where md5(qa_id+feed_time) in
(
select md5(qa_id+feed_time)from feedback fb
group by fb.qa_id
having count(fb.qa_id) > 1
order by fb.qa_id
desc
)
It fails saying:
Error Code : 1093
You can't specify target table 'feedback' for update in FROM clause
(0 ms taken)
that I cannot select and delete from the same table (i.e. if it appears in a sub-query). I don't have much expertise in databases and I am not allowed to use any programming language to do the same. :(
Any suggestions on how I can do this with queries only?
The easiest way to fix this in MySQL is to put the subquery in an additional layer of subquery:
delete from feedback
where md5(qa_id+feed_time) in (select *
from (select md5(qa_id+feed_time)
from feedback fb
group by fb.qa_id
having count(fb.qa_id) > 1
) t
)