I have a table with a empty column, and I want to insert values in this column according to the value of another column, something like that:

INSERT INTO table_name(color)
VALUE('blue')
WHERE number = 1
VALUE('red')
WHERE number = 2
VALUE('green')
WHERE number = 3

I know this query not work, it's just to exemplify what I want :)

CodePudding user response：

You need an UPDATE, not an INSERT

See example

CREATE tABLE table_name (`number` int , color varchar(10))

INSERT INTO table_name(`number`)
VALUES(1),(2),(3);

SELEcT 
* FROM table_name

number | color
-----: | :----
     1 | null 
     2 | null 
     3 | null 

UPDATE table_name 
SET color = case
when number = 1 then 'blue' when number = 2 then 'red'
when number = 3 then 'green' else color end

SELEcT 
* FROM table_name

number | color
-----: | :----
     1 | blue 
     2 | red  
     3 | green

db<>fiddle here