Updating a table column with numeric value sql

Databases
1

Hi all
I have a mysql table called vehicle_type and it has a column called cylinder_id
The value in cylinder_id is a number that is defined by the value in the column cylinders.

I want to update all the values in cylinder_id to 1 where the value in cylinders is between 0-99

This is my attempt- what am I doing wrong? Thank you

UPDATE CYLINDER_ID '1'
VEHICLE_TYPE WHERE
CYLINDERS 0>99
2

you were just guessing, weren’t you

UPDATE vehicle_type
   SET cylinder_id = 1
 WHERE cylinders BETWEEN 0 AND 99

:slight_smile:

3

Thank you Rudy! I am now updating cylinder_id when it is 3 when the vclass is 1 and the cylinders are 200 or more. Does this code look correct? Thank you

UPDATE vehicle_type
SET cylinder_id = 3
WHERE vclass = 1 and cylinders >200

4

what happened when you tested it? :wink:

5

Here is the message so it appears it hasn’t changed anything, the valuemust already be correct:

[SQL]Affected rows: 0
Time: 0.401ms

6

you can double-check it by running this –


SELECT DISTINCT cylinder_id
  FROM vehicle_type
 WHERE vclass = 1
   AND cylinders > 200
7

I get 3 as a result, I have done a new query with but it doesn’t affect any rows, I have checked that there are values in the database that are vclass 1 and have cylinders with value 200. They are not being updated and I don’t understand why - maybe I have to do change the cylinders to >199 so it will update those values?

UPDATE vehicle_type
SET cylinder_id = 3
WHERE vclass = 1 and cylinders >200

8

yes

alternatively, use

cylinders [B][COLOR="Blue"]>=[/COLOR][/B] 200
9

Thanks :slight_smile: