Mysql - Update record from row X to row Y?

Question

Say I have 20 records, and I want to update row 6 to row 10. I have tried this code but getting an error:

UPDATE db_scheduling.tbl_student 
  SET section ='a' 
  WHERE yrlevel = '1' 
  ORDER BY grade DESC 
  LIMIT 5 
  OFFSET 5;

It says I have error on the OFFSET part and I don't know if it works in UPDATE query. When I remove the OFFSET 5, it just updates the first 5 rows. Help please.

possible duplicate of [update multiple rows using limit in mysql?](http://stackoverflow.com/questions/1513206/update-multiple-rows-using-limit-in-mysql) — Strawberry, Apr 30 '15 at 05:22

score 1 · Answer 1 · edited May 23 '17 at 12:14

1

you can use something like this:

UPDATE db_scheduling.tbl_student  
    SET section ='a'
    WHERE yrlevel IN (
     SELECT id FROM (
         SELECT yrlevel FROM db_scheduling.tbl_student 
         ORDER BY grade DESC  
         LIMIT 5, 5
     ) tmp
    );

Have a look update multiple rows using limit in mysql?

edited May 23 '17 at 12:14

Community

1
1

answered Apr 30 '15 at 04:40

Supravat Mondal

2,574
2
22
33

@LeeSong, perhaps, but clearly something like it works – Strawberry Apr 30 '15 at 05:22

score 0 · Answer 2 · answered Apr 30 '15 at 05:44

0

You can also use a self join with only rows that you want to update

UPDATE `tbl_student` s
JOIN (
SELECT id FROM `tbl_student` 
WHERE yrlevel = '1' 
  ORDER BY grade DESC 
  LIMIT 5 
  OFFSET 5
) s1 USING(id)
SET s.section ='a'

DEMO

answered Apr 30 '15 at 05:44

M Khalid Junaid

63,861
10
90
118

Mysql - Update record from row X to row Y?

2 Answers2