PHP PDO : Multiple UPDATE in a single query

Question

I'm trying to get the following to run via a single pdo statement

UPDATE `coin_price` SET `coin_id` = 1 WHERE coin_id = 1;
UPDATE `coin_price` SET `coin_id` = 178 WHERE coin_id = 178;

and I am getting the following exception

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE `coin_price` SET `coin_id` = 178 WHERE coin_id = 178'` at line 1

I am just running

$statement = $this->system[$location]["connection"]->prepare($sql);
$statement->execute();

and getting an exception. I tried with binding and without.

Is what I am doing not possible?

Note : Both queries run fine via PDO by themselves

Why are you setting `coin_id` to the same value it already has? — Barmar, Jul 14 '20 at 22:59
There is no point running multiple update queries when you can handle it in one. Also setting coin_id to a value it already is assigned doesn't makes sense. — Ankit Jindal, Jul 15 '20 at 02:05
I don't want to use ON DUPLICATE KEY as I would like to maintain incremental IDs @cetver — hendr1x, Jul 15 '20 at 03:04

score 2 · Answer 1 · answered Jul 15 '20 at 01:39

Don't try to run multiple statements at once.

Run them separately. But, if you need transactional semantics, surround by START TRANSACTION and COMMIT. (Example: You don't want a crash between debiting one financial account and crediting another.)

Another plan is to write and CALL a Stored Procedure to combine the statements.

If you are concerned about performance, well, you are talking about very few milliseconds.

score 1 · Answer 2 · answered Jul 14 '20 at 23:00

1

You can only execute one query at a time. But you can combine them into a single query:

UPDATE coin_price
SET coin_id = 
    CASE coin_id
        WHEN 1 THEN 1
        WHEN 178 THEN 178
    END
WHERE coin_id IN (1, 178)

answered Jul 14 '20 at 23:00

Barmar

741,623
53
500
612

Thank you for your reply. Is it possible to expand this to update as many fields as wanted? – hendr1x Jul 15 '20 at 03:05
Yes, you can assign multiple columns, and each of them can contain a `CASE` expression to assign the appropriate value. – Barmar Jul 15 '20 at 14:40
See the duplicate question for examples. – Barmar Jul 15 '20 at 14:41

PHP PDO : Multiple UPDATE in a single query

2 Answers2