PHP PDO : Multiple UPDATE in a single query [duplicate]

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
– Barmar, Commented Jul 14, 2020 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
– Ankit Jindal, Commented Jul 15, 2020 at 2:05
I don't want to use ON DUPLICATE KEY as I would like to maintain incremental IDs @cetver — hendr1x
– hendr1x, Commented Jul 15, 2020 at 3:04

Rick James · Accepted Answer · 2020-07-15 01:39:31Z

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.

Barmar · Accepted Answer · 2020-07-14 23:00:49Z

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, 2020 at 23:00

Barmar

789k57 gold badges555 silver badges669 bronze badges

3 Comments

hendr1x Over a year ago

Thank you for your reply. Is it possible to expand this to update as many fields as wanted?

Barmar Over a year ago

Yes, you can assign multiple columns, and each of them can contain a CASE expression to assign the appropriate value.

Barmar Over a year ago

See the duplicate question for examples.

Collectives™ on Stack Overflow

PHP PDO : Multiple UPDATE in a single query [duplicate]

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related