mysql - 修复 MySQL 查询语法错误 (LIMIT)

Question

我有一张表 'tbl_content'

当我运行以下查询时，我可以看到 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

谁能给个解决方案？

Answer 1

您可以尝试对带限制的子选择使用 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