How can I update multiple rows with one query (perl-mysql) ?

Question

I need to update multiple rows with one query. For the insert we usually do something like

INSERT INTO `table` (c1,c2,c3) VALUES 
 (1,2,3),
 (4,5,6),
 ..

Yet how can we do something similar for the update where each row has different values than the other? And there is a condition that is related to the id of each row?

Any one faced similar issue?

Example of how I have to do the update now is:

UPDATE questions
SET lab='What sections do you believe the site must not have?',
    type='textarea',
    options=''
WHERE rnum=11;

UPDATE questions
SET lab='What is your favourate section?',
    type='radio',
    options='section1,section2,section3,section4,section5'
WHERE rnum=12;

And so on. Definitely this is the worst way to do it because every query needs to be executed, and there may be as many as 20.

Answer 1

Consider follwing example it increment all the the values of column 'col_name' whose id > 5 and col_name not 0

id    col_name
1       1
5       2
6       3
7       5
8       6
9       7
10      0

Query

update tbl_name SET col_name=col_name+1 where id > 5 and col_name !=0

O/P will be

id    col_name
1       1
5       2
6       4
7       6
8       7
9       8
10      0

Answer 2

If you're updating one table with values from another table, it's easy:

update table1,table2
SET table1.value = table2.value
WHERE table1.key = table2_foreign_key

If you're updating one table by changing a subset of rows in a consistent manner, it's easy:

update table1
SET table1.value = (table1.value * 2)
WHERE table1.id in (SELECT id from table1 where table1.key > 50);

If you're trying to update multiple records in one table where each record is updated differently, without using a second table, it's basically impossible. Better off doing it in your code.

Answer 3

Using placeholders, you can do it with many executions of the same query:

my @data = (
  [ 'new_lab1', 'new_type1', 'new_opt1', 1 ],
  [ 'new_lab2', 'new_type2', 'new_opt2', 2 ],
);

my $sql = <<EOT;
UPDATE questions
SET lab=?,
    type=?,
    options=?
WHERE rnum=?
EOT

my $sth = $dbh->prepare($sql);
for my $datum (@data) {
    $sth->execute(@$datum);
}

Answer 4

First, why do you need to do this in one query? If you need the updates done atomically, can you wrap them in a START TRANSACTION; and COMMIT;? That's probably what I would do.

However, if you really need this done in one query, and the number of rows you're updating is reasonably small, it can be done! You can probably use this ugly little trick:

UPDATE questions
SET
lab = IF(rnum=11, 'What sections...?',
        IF(rnum=12, 'What is your...?',
          IF(rnum=13, 'Etc.',
            NULL
          )
        )
      ),
type = IF(rnum=11, 'textarea',
         IF(rnum=12, 'radio',
           IF(rnum=13, 'Etc.',
             NULL
           )
         )
       )
WHERE rnum IN (11, 12, 13);

Writing the loop in perl to construct that freak of nature is left as an exercise :)

If you write it correctly and if the table is as you believe, those leftover NULLs should never be used. For added safety, you may wish to declare some of those columns to be NOT NULL, if you're able, so that if your rnum doesn't match, the assignment will cause the constraint to fail and the UPDATE will be aborted.