修复mysql查询语法错误(限制)

5kgi1eie 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(292)

我有一张“tbl\ U内容”的table
当我运行下面的查询时，我可以看到11条记录/数据

SELECT ContentID, CategoryID, Home, Inner
FROM tbl_content
WHERE CategoryID = 1 AND Inner = 2
ORDER BY ContentID DESC

当我运行以下查询时，我可以看到前4条记录/数据

SELECT ContentID, CategoryID, Home, Inner
FROM tbl_content
WHERE CategoryID = 1 AND Inner = 2
ORDER BY ContentID DESC LIMIT 7, 10

我想更新剩余的前4条记录/数据的值。但不幸的是，它正在产生错误。

UPDATE tbl_content
SET Inner = 1
WHERE CategoryID = 1 AND Inner = 2
ORDER BY ContentID DESC LIMIT 7, 10

有人能给出解决办法吗？

mysql

来源：https://stackoverflow.com/questions/52041636/fix-mysql-query-syntax-error-limit

2条答案

按热度按时间

0dxa2lsx1#

试试这个。

UPDATE tbl_content SET Inner=1 WHERE id IN(SELECT ContentId FROM (SELECT ContentID, CategoryID, Home, Inner FROM tbl_content WHERE CategoryID=1 AND Inner=2 ORDER BY ContentID DESC LIMIT 7, 10));

赞(0）回复(0）举报 2021-06-21

6uxekuva2#

您可以尝试为subselect和limit使用in子句

UPDATE tbl_content SET Inner=1 
WHERE CategoryID=1 AND Inner=2 
AND ContentID  IN ( 
 select contentID from (
  select ContentID  
  from  tbl_content 
  order by ORDER BY ContentID DESC LIMIT 7, 10 ) t

)

或者加入

UPDATE tbl_content c 
INNER JOIN (
  select ContentID  
  from  tbl_content 
  order by ORDER BY ContentID DESC LIMIT 7, 10 
) t  on t.ContentID = c.ContentID
SET c.Inner=1 
WHERE c.CategoryID=1 AND c.Inner=2

赞(0）回复(0）举报 2021-06-20

我来回答

修复mysql查询语法错误(限制)

2条答案

相关问题

热门标签

最新问答