CODEWITHSUNDEEP
mysqlsql
C_Z_
C_Z_

Reputation: 7796

UPDATE SET column with different values per row

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 |

Upvotes: 4

Views: 593

Answers (2)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

@Strawberry's answer is exemplary indeed; but what if the id values are non-continuous.

For instance, the id values are 2, 5, 6. In this case, we can emulate a continuous new_id using Row_Number() functionality in MariaDB 10.2+ / MySQL 8+. I also noticed that your input comma-separated string is having spaces after comma. To handle that (variable amount of spacing), we can use Trim() function after the substring operation. Building upon excellent answer by @Strawberry:

Schema (MySQL v8.0) - View on DB Fiddle

CREATE TABLE your_table_name
(id INT UNSIGNED PRIMARY KEY
,val1 VARCHAR(12) NOT NULL
,val2 VARCHAR(12) NOT NULL
);

INSERT INTO your_table_name VALUES
(2,'foo1','bar'),
(5,'foo2','baz'),
(6,'foo3','bam');

Update Query

UPDATE your_table_name t1
       JOIN (SELECT Row_number()
                      OVER (
                        ORDER BY id) AS new_id,
                    id
             FROM   your_table_name) t2
         ON t2.id = t1.id
SET    val1 =
Trim(Substring_index(Substring_index('spam, eggs,  ham', ',', t2.new_id), ',', -1));

Check Data

SELECT * FROM your_table_name;

| id  | val1 | val2 |
| --- | ---- | ---- |
| 2   | spam | bar  |
| 5   | eggs | baz  |
| 6   | ham  | bam  |

---

In older versions of MySQL/MariaDB, the window functions are not available. In that case, we can utilize user-defined variables to emulate new_id:

Schema (MySQL v5.7) - View on DB Fiddle

CREATE TABLE your_table_name
(id INT UNSIGNED PRIMARY KEY
,val1 VARCHAR(12) NOT NULL
,val2 VARCHAR(12) NOT NULL
);

INSERT INTO your_table_name VALUES
(2,'foo1','bar'),
(5,'foo2','baz'),
(6,'foo3','bam');

Update Query

UPDATE your_table_name t1
       JOIN (SELECT @rn := @rn + 1 AS new_id, 
                    id 
             FROM   your_table_name 
             CROSS JOIN (SELECT @rn := 0) vars
             ORDER BY id) t2
         ON t2.id = t1.id
SET    val1 =
Trim(Substring_index(Substring_index('spam, eggs,  ham', ',', t2.new_id), ',', -1));

Check Data

SELECT * FROM your_table_name;

| id  | val1 | val2 |
| --- | ---- | ---- |
| 2   | spam | bar  |
| 5   | eggs | baz  |
| 6   | ham  | bam  |

Upvotes: 5

Strawberry
Strawberry

Reputation: 33935

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  |
+----+------+------+

Upvotes: 6

Related Questions