how to get rows between two string values in mysql

Question

My table looks like this:

id   city         road_id
-------------------------
1    london       3
2    manchester   3
3    newcastle    3
4    glasgow      3
5    london       5
6    newcastle    5

I know values of two cities and road_id and need something like this:

UPDATE table SET anothercolumn=1 WHERE id>=(id for)london AND id<(id for)glasgow AND road_id=3

to affect only these rows:
1    london       3
2    manchester   3
3    newcastle    3

So you want to select (in this case, for update) the rows with the id between id(London, inclusive) and id(Glasgow, exclusive)--rather than cities with names between "London" and "Glasgow"? — Jonathan Eunice
– Jonathan Eunice, Commented Dec 14, 2014 at 22:13

juergen d · Accepted Answer · 2014-12-14 22:13:41Z

1

UPDATE your_table 
SET anothercolumn = 1 
WHERE id >= (select id from your_table where city = 'london')
AND   id <  (select id from your_table where city = 'glasgow')
AND road_id = 3

answered Dec 14, 2014 at 22:13

juergen d

205k40 gold badges303 silver badges376 bronze badges

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

Collectives™ on Stack Overflow

how to get rows between two string values in mysql

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related