Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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
add comment

2 Answers

up vote 6 down vote accepted

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>
share|improve this answer
add comment

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);
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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