UPDATE SET column with different values per row

Question

If I have a table my_table like this:

| id | val1 | val2|
| 1  | foo1 | bar |
| 2  | foo2 | baz |
| 3  | foo3 | bam |

And I have a hard-coded comma-separated list of values that I have generated programmatically: spam, eggs, ham.

I want to insert my values into the column val1. I am imagining something like this:

UPDATE my_table SET val1 = SELECT * FROM (VALUES ('spam'),('eggs'),('ham'))

But this gives me a syntax error (MySQL 5.6.44)

Final result should look like this:

| id | val1 | val2|
| 1  | spam | bar |
| 2  | eggs | baz |
| 3  | ham  | bam |

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,val1 VARCHAR(12) NOT NULL
,val2 VARCHAR(12) NOT NULL
);

INSERT INTO my_table VALUES
(1,'foo1','bar'),
(2,'foo2','baz'),
(3,'foo3','bam');

UPDATE my_table SET val1 = SUBSTRING_INDEX(SUBSTRING_INDEX('spam,eggs,ham',',',id),',',-1);

SELECT * FROM my_table;
+----+------+------+
| id | val1 | val2 |
+----+------+------+
|  1 | spam | bar  |
|  2 | eggs | baz  |
|  3 | ham  | bam  |
+----+------+------+