9

What is the easiest way to update many rows in a table? I have a csv file that looks like this:

|primary_key |value|
|          1 |  xyz|
|          2 |  abc|
|          3 |  def|
...

Rows with these primary keys already exist in the target table

I would like to update the target table with these values. Is there syntax so that I can write something like:

update mytable set value = ('xyz', 'abc', 'def') where primary key = (1,2,3);

Looking through the MySQL Update Reference, this site (MySQL - csv update), SO (update multiple rows, multiple db updates, update multiple rows), I suspect that the answer is "no", but I'd like to confirm that this is true.

  • I added the MySQL tag assuming that your references to the MySQL documentation implied that was the database you're using. – Justin Cave Mar 27 '12 at 22:29
9

First here is sample data

mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (
    ->     id int not null,
    ->     value VARCHAR(255),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable (id) values (1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | NULL  |
|  2 | NULL  |
|  3 | NULL  |
+----+-------+
3 rows in set (0.00 sec)

mysql>

Here is the new query

update mytable A inner join
(
    SELECT 1 id,'xyz' value UNION
    SELECT 2   ,'abc'       UNION
    SELECT 3   ,'def'

) B USING (id)
SET A.value = B.value;

Here is the new query executed

mysql> update mytable A inner join
    -> (
    ->     SELECT 1 id,'xyz' value UNION
    ->     SELECT 2   ,'abc'       UNION
    ->     SELECT 3   ,'def'
    -> ) B USING (id)
    -> SET A.value = B.value;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | xyz   |
|  2 | abc   |
|  3 | def   |
+----+-------+
3 rows in set (0.00 sec)

mysql>
3

Assuming that you don't want to load the data from the CSV file into a database table and then do a correlated UPDATE,

UPDATE mytable t
   SET value = (SELECT value
                  FROM tbl_with_csv_data csv
                 WHERE csv.primary_key = t.primary_key)
 WHERE EXISTS( SELECT 1
                 FROM tbl_with_csv_data csv
                 WHERE csv.primary_key = t.primary_key)

then you should be able to use a CASE

UPDATE mytable t
   SET value = CASE WHEN primary_key = 1 THEN 'xyz'
                    WHEN primary_key = 2 THEN 'abc'
                    WHEN primary_key = 3 THEN 'def'
                    ELSE value
                END
 WHERE primary_key IN (1,2,3);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.