Error Code: 1093. Table 'site_html' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Question

I want to update the 'status' column of the table with respective id , but it leads me to error code 1093. Below is my SQL Query

Update site_html Set status='Update Found' 
Where id = (
            select id 
            from site_html
            where link='http://www.example.com');

How can i correct this error? I am new to SQL .

Your answer is here I guess: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause *** or here: http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/14302701#14302701 *** it appears your query is not match with MySQL standarts. But there are workarounds for this task, of course. — Ali1928, Jun 13 '16 at 03:14

score 3 · Accepted Answer · answered Jun 13 '16 at 03:28

In MySQL, you can't modify the same table name directly which you use in the SELECT part. So you can do it through table alias.

update site_html AS s, (select id  from site_html where link='http://www.example.com') AS t
set status='Update Found' 
where s.id = t.id;

Error Code: 1093. Table 'site_html' is specified twice, both as a target for 'UPDATE' and as a separate source for data

1 Answers1