mysql中You can’t specify target table for update in FROM clause错误解决方法

mysql中You can’t specify target table for update in FROM clause错误的意思是说，不能先select出同一表中的某些值，再update这个表(在同一语句中)。 例如下面这个sql：

delete from tbl where id in
(
        select max(id) from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
)

改写成下面就行了：

delete from tbl where id in
(
    select a.id from
    (
        select max(id) id from tbl a where EXISTS
        (
            select 1 from tbl b where a.tac=b.tac group by tac HAVING count(1)>1
        )
        group by tac
    ) a
)

下面记录下把所有内容与某一个内容相同的选择出来再更新的SQL语句如下:

UPDATE `yi`.`data_content_254` SET `已发` = ’1’ WHERE  `data_content_254`.`Id` in 
(
select a.`Id` from (
SELECT `data_content_254`.`Id` FROM `yi`.`data_content_254` where `data_content_254`.`内容` = (SELECT `data_content_254`.`内容`
FROM `data_content_254`
WHERE `data_content_254`.`Id` =6794)
) a
)

另外顺便记录一下，wordpress博客删除标题为一样的文章的sql批量删除语句:

--删除标题相同的文章

DELETE a . * FROM wp_posts AS a INNER JOIN (
SELECT post_title, MIN( id ) AS min_id
FROM wp_posts
WHERE post_type = ’post’
AND post_status = ’publish’
GROUP BY post_title
HAVING COUNT( * ) >1
) AS b ON b.post_title = a.post_title
AND b.min_id <> a.id
AND a.post_type = ’post’
AND a.post_status = ’publish’

另外选择出内容与某个id内容相同的所有文章

--内容与29560相同的文章

SELECT *
FROM `wp_posts`
WHERE `post_content` = (SELECT `post_content`
FROM `wp_posts`
WHERE `ID` =29560 )

喜欢 (1)赏

[🍬谢谢你请我吃糖果🍬🍬～]

分享 (0)