1

I want to update multiple rows in a custom table using just one query.

Each row has its own id (obviously) and different fields/values to update.

Given this question and this post, i think i can achieve it with a single db_query, similar to this one:

UPDATE {table} SET

`field1` = CASE `id`
    WHEN 1 THEN 'field1 value 1'
    WHEN 2 THEN 'field1 value 2'
    END

`field2` = CASE `id`
    WHEN 1 THEN 'field2 value 1'
    WHEN 2 THEN 'field2 value 2'
    END

WHERE `id` IN(1, 2);

Or:

INSERT INTO {table} (`id`, `field1`, `field2`)
VALUES
    (1, 'field1 value 1', 'field2 value 1'),
    (2, 'field1 value 2', 'field2 value 2'),
ON DUPLICATE KEY UPDATE
    `id` = VALUES(`id`),
    `field1` = VALUES(`field1`),
    `field2` = VALUES(`field2`);

But i'ld feel more confortable using db_update instead of db_query.

Any suggestions? Thanks

2 Answers 2

0

You can use below example to update:

    $items = array(1,2);
    db_update('your_table')->fields(array(
      'update_field_one' => $to_update_one,
      'update_field_two' => $to_update_two,
      'updated' => REQUEST_TIME,
    ))->condition('id', $items, 'IN')->execute();
1
  • thanks for the answer but this would set the same value for field one/two etc for every row, while i want to save a different value in each field for each row
    – Giona
    Jul 29, 2016 at 13:06
0

You try the code update:

foreach ($updates as $key => $value) {
  $fields = array('some_field' => $value);
  db_update('your_table')->fields($fields)->condition('some_id', $key)->execute();
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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