How to select complex data from a table and delete it from the same table

Question

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?

Checkout out the link later on this comment, it gives you the answer. Is your table innodb or MySIAM? http://stactkoverflow.com/questions/10554627/delete-from-having-count-in-mysql — Yoram de Langen, Jul 15 '13 at 10:33
Oke, just checking. because mysiam locks table when doing a insert, update or delete etc. but it is also not possible to do it you'r way checkout the link :) — Yoram de Langen, Jul 15 '13 at 10:36
@yoramdeLangen !!! can't access the page now .... i was able to do that a minute ago... — Mono Jamoon, Jul 15 '13 at 10:42
Sorry, was a typo in the link: http://stackoverflow.com/questions/10554627/delete-from-having-count-in-mysql — Yoram de Langen, Jul 15 '13 at 10:46

score 3 · Accepted Answer · answered Jul 15 '13 at 11:02

3

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
                              )

answered Jul 15 '13 at 11:02

Gordon Linoff

1,242,037
58
646
786

LOL...what kind of sorcery is this!!?? Making fool out of MySQL. :D – Mono Jamoon Jul 15 '13 at 11:08
2

@RickeshJohn . . . The additional level of subquery forces MySQL to instantiate the table (or at least for the compiler to realize that the table is instantiated, bypassing the warning). – Gordon Linoff Jul 15 '13 at 11:12

How to select complex data from a table and delete it from the same table

1 Answers1