designerliebe designerliebe -3 years ago 143
MySQL Question

MySQL: Change value from '0' to '1' in a table - ONLY CERTAIN ROWS

I try to change the value from '0' to '1' of a column in my table. But I need the change only between row 1 and 100.

This is the status quo.

+-------+--------+
| pid | value |
| 1 | 0 |
| 2 | 0 |
| .. | 0 |
| 100 | 0 |
| 101 | 0 |


The table should be:

+-------+--------+
| pid | value |
| 1 | 1 |
| 2 | 1 |
| .. | 1 |
| 100 | 1 |
| 101 | 0 |


I tried this:
SELECT * FROM vxg_posts WHERE pid BETWEEN 1 AND 10088


In the column "validated" I try to set the value to 1. But only between 1 and 10088

Answer Source

You can use the WHERE clause in an UPDATE statement. The syntax is documented here.

In your case it would be something like:

UPDATE vxg_posts
SET value = 1
WHERE pid BETWEEN 1 AND 100

It's also possible to use the LIMIT clause:

UPDATE vxg_posts
SET value = 1
ORDER BY pid ASC
LIMIT 100

If the values in pid column are consecutive, the two statements will do the same.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download