1

I need to update the column "myColumn" in "myTable" for rows 5 to 10. I thought I can do the following but apparently it's giving me a syntax error:

UPDATE myTable SET myColumn = 'mamal'  LIMIT 5,10;

Thanks

Mohammad
  • 1,078
  • 2
  • 18
  • 39

5 Answers5

3

SQL tables represent unordered sets. If you have a primary key, you can use that for the ordering.

MySQL does allow limit, but not offset, so you could update the first five rows:

UPDATE myTable
    SET myColumn = 'mamal'
    ORDER BY id
    LIMIT 5;

But not with an offset.

You could get around this with a JOIN:

UPDATE mytable t JOIN
       (SELECT id
        FROM mytable tt
        ORDER BY id
        LIMIT 5, 10
       ) tt
       ON tt.id = t.id
    SET myColumn = 'mamal';
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

You can select and update column value based on primarykey of your table.

if you want to change rows limit means you can change limit values.

in below code id Is nothing but Primay Key of your table

UPDATE myTable SET myColumn='mamal' WHERE id IN ( SELECT id FROM ( SELECT id FROM myTable where id ORDER BY id ASC LIMIT 5,5 ) tmp )
Magnil Amar
  • 84
  • 7
1

TRY by using subquery and join as below and please replace the join column in <column> with real one

UPDATE myTable mt
INNER JOIN (SELECT <column> FROm myTable LIMIT 5,10) t ON t.<column> = mt.<column>
SET mt.myColumn = 'mamal'
Shushil Bohara
  • 5,556
  • 2
  • 15
  • 32
1

Try this:

update myTable 
set myColumn = 'mamal' 
where your_primary_key 
in (select * from (select your_primary_key from myTable limit 5,5) as t);

MySQL does support offset in a limit clause, but only for SELECT. So the correct syntax is: LIMIT offset,row_count. In this case it should be limit 5,5, not limit 5,10. Check: Select Syntax.

You can also use LIMIT in UPDATE, but you can only specify the row_count in this case. Check: Update Syntax.

If you don't have a primary key, just use any unique key.
For why you need a select * from here, it's just a trick to bypass the error discribed here.

walter
  • 1,199
  • 7
  • 13
1

I ran into the same problem and saw the answers but after researching I found that you can use "BETWEEN" to UPDATE the Table.

UPDATE myTable 
SET myColumn = "mamal"
WHERE id BETWEEN 5 AND 10;
Muhammad Ahmad
  • 84
  • 6