It is not possible to update (UPDATE) the same table that you use in the SELECT query
Today I came across a very unpleasant bug in MySQL. When executing the following query in MySQL 5.0.45:
Got an error:
# 1093 - you can not specify target table for files in FROM clause
As it turned out the case in bug number 6980 . In MySQL, you cannot change (including DELETE) the same table that you use in a SELECT query. This behavior is documented at dev.mysql.com/doc/mysql/en/UPDATE.html . The bug was discovered in MySQL 4.1.7, but it has not been fixed to this day.
One way out is to use multiple queries .
')
Or you can use a temporary table in a subquery :
But this method is very ugly and badly affects performance. And, unfortunately, it will not work if you refer to the temporary table more than once ...
UPDATE `files` SET `file_md5` =
(
SELECT MD5( `file_blob` )
FROM `files`
WHERE `id`= 6
)
WHERE `id` = 6
Got an error:
# 1093 - you can not specify target table for files in FROM clause
As it turned out the case in bug number 6980 . In MySQL, you cannot change (including DELETE) the same table that you use in a SELECT query. This behavior is documented at dev.mysql.com/doc/mysql/en/UPDATE.html . The bug was discovered in MySQL 4.1.7, but it has not been fixed to this day.
One way out is to use multiple queries .
')
Or you can use a temporary table in a subquery :
UPDATE `apples`
SET `price` = (
SELECT `price` FROM (
SELECT * FROM `apples`
) AS x
WHERE `variety` = 'gala')
WHERE `variety` = 'fuji';
But this method is very ugly and badly affects performance. And, unfortunately, it will not work if you refer to the temporary table more than once ...
Source: https://habr.com/ru/post/49531/
All Articles