0

I'm having a strange issue with the updating multiple rows in one query. Take the following example:

UPDATE `custom_pricing` SET `price_per_unit` = 123456 WHERE `price_id` = 21688;
UPDATE `custom_pricing` SET `price_per_unit` = 69 WHERE `price_id` = 80085;

Apparently if I use more than one UPDATE statement at a time, only the last statement works. So in the above only my product_id = 80085 gets updated. Of course, both work when used independently.

I just did a similar query to another database on the same server and it worked just fine. Could there be a settings or limitation to a specific database that might effect this? Why doesn't my sql work?

jamil
  • 555
  • 5
  • 18
  • 1
    Most database APIs only allow one query per execute unit; that is two queries; not "multiple rows in one query": ...it is odd that another database on the same server seems to allow it though; are you using different connections? (Some connection types optionally allow multiple queries; some ODBC drivers actually have a checkbox to allow that kind of behavior.) – Uueerdo Jan 08 '19 at 18:34
  • 1
    Another question would be which tool you are using to execute the queries. If executing through command line tool it expects only one query to be executed. Only if you are using a GUI tool you would be able to execute multiple queries. What `Uueerdo` has pointed out is correct. Its DB tools which take your queries and run it. Multiple queries would run as a batch. the tool will run one query at a time. – Acewin Jan 08 '19 at 19:07
  • I'm using the same connection (root, localhost) via Sequel Pro, these are Magento 2 websites on my local dev environment. Eventually I want to do ~ 3k updates at once with a single `.sql` file, which is what worked on the first database. – jamil Jan 08 '19 at 19:13
  • I suggest the way to run an `.sql` file is using the `mysql` client. See my answer to https://stackoverflow.com/questions/4027769/running-mysql-sql-files-in-php/4028289#4028289 – Bill Karwin Jan 08 '19 at 22:09

0 Answers0