About
SQL - Update in MySql
Support
You can't specify target table 'pages' for update in FROM clause
SQL Error [1093] [HY000]: You can't specify target table 'pages' for update in FROM clause
caused by the following statement
update `pages`
set crawled_ts = null
where crawled_ts >= (
SELECT crawled_ts from pages
where url_hash = md5('https://example.com/page'
)
);
because the updated tables (pages) is in the where clause
Solution: Wrap the select in the where clause, one level further (in the tree). Example:
update `pages`
set crawled_ts = null
where crawled_ts >= (
select crawled_ts from (
SELECT
crawled_ts
FROM
pages
WHERE
url_hash = md5('https://example.com/page'
) a
)
);