MySQL Can’t specify target table for update in FROM clause

While running the following update query:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://localhost:9000/wp-content/', 'http://localhost:9200/wp-content/')
WHERE ID IN
(
	SELECT ID FROM wp_posts WHERE `post_content` LIKE '%http://localhost:9000/wp-content/%'
);

I was getting the folowing error:

Error Code: 1093. You can’t specify target table .. for update in FROM clause

The solution was to wrap the inner query with in one more temp query:

UPDATE wp_posts SET post_content = REPLACE(post_content, 'http://localhost:9000/wp-content/', 'http://localhost:9200/wp-content/')
WHERE ID IN
(
	SELECT ID FROM
	(
		SELECT ID FROM wp_posts WHERE `post_content` LIKE '%http://localhost:9000/wp-content/%'
	) AS TEMP
);

The above query ran without any error. The change was to add:

SELECT ID FROM
(...) As Temp

around the sub query.

Related Posts: