Date: 07/29/05 (MySQL Communtiy)    Keywords: mysql, sql, web, google

I am trying to execute a delete using a subquery but I keep getting the same error and google and mysql.com are both no use as to what the error actually means

My query is this

delete from exon where exon_id in(select exon.exon_id from exon left join exon_transcript on exon.exon_id = exon_transcript.exon_id where exon_transcript.exon_id is NULL);


and this is the error

ERROR 1093 (HY000): You can't specify target table 'exon' for update in FROM clause

do you have any ideas what I am doing wrong?

exon_transcript is a table which links the exon table with another table called transcript and there were a series of rows missing from exon transcript so the links weren't present

I have been able to use other methods to delete the links from transcript but I can't see any other way of deleting the links from exon

if you want to see the schema description have a look here

http://cvsweb.sanger.ac.uk/cgi-bin/cvsweb.cgi/ensembl/sql/table.sql?rev=1.229&view=log

I am using this version of mysql

mysql Ver 14.7 Distrib 4.1.12, for dec-osf5.1b (alphaev68) using readline 4.3

thanks

Source: http://www.livejournal.com/community/mysql/64885.html