Fix MySQL query syntax error (LIMIT)

Question

I have a table 'tbl_content'

When I run the following query I can see 11 records/data

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

When I run the following query I can see first 4 records/data

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

I want to update value of the remaining first 4 records/data. But unfortunately it is generating error.

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

Can anyone kindly give any solution?

ScaisEdge · Accepted Answer · 2018-08-27 14:41:54Z

4

You could try uisng an IN clause for subslect with limit

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

)

or a join

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

edited Aug 27, 2018 at 14:41

answered Aug 27, 2018 at 14:39

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ScaisEdge Over a year ago

@TimBiegeleisen .. probably you are right .. anyway wrapped the subquery for avoid problem in the IN sample .. thanks

eMythMakers.com Over a year ago

Your first solution updates records from number 5th to 8th. And your last solution updates first 4 records. But I wanted to keep first 7 records intact and change last /rest of the records.

maziyank · Accepted Answer · 2018-08-27 14:41:04Z

1

try this.

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));

answered Aug 27, 2018 at 14:41

maziyank

6163 silver badges10 bronze badges

Collectives™ on Stack Overflow

Fix MySQL query syntax error (LIMIT)

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related