How to form 1 UPDATE query with a thousand values?

Question

SQL

Good afternoon!
Once a day, my script receives information (about a thousand lines in the database), which needs to be changed in this very database. Simply put, these are goods whose prices change every day. An array comes with prices and product IDs. It is necessary to form one UPDATE request, in which there will be something like: Item A: price = 500, shipping = 100. Item B: price = 400, shipping = 50. And so on. About 1000 items so. Sending one request is not an option.
Thank you in advance!

Answer 1

How to form 1 UPDATE query with a thousand values?

Like this, and so on up to 1000 (or as many as you need):

UPDATE table1
  SET val = CASE
    WHEN id = 1 THEN 10
    WHEN id = 2 THEN 20
    WHEN id = 3 THEN 30
  END
  -- тут ещё можно дописать условие, типа WHERE id IN (1,2,3)

Sending one request is not an option.

In my opinion, you are trying to turn the poor dolphin inside out through ... You can send many requests at once, separating them with ";" (semicolon). Moreover, they can also be wrapped in a transaction (both many requests one at a time, and many requests through ";").
PS An example of a table on which experiments were carried out: (just in case)

Answer 2

CREATE TEMPORARY TABLE ...;
INSERT INTO ... (...), (...), (...);
UPDATE ...;
DROP TEMPORARY TABLE ...;

Total 4 requests.

Answer 3

see here , but the Update request itself is slow, and cramming everything into one expression will not make it faster, rather, on the contrary,
you need to change the application logic itself - for example, a column with a date and current prices, another storage, etc.

Answer 4

A more or less optimal option in a similar solution (updating prices for 0.5-20 million positions):
1. data is drawn from an external source into a temporary table
2. missing (new) positions are added from the temporary table to the nomenclature
3. the temporary table is thinned for duplicates 4. the temporary table is thinned
out according to a number of interactive conditions (which fields from it to use for updating) - matching ones are removed
.
experimentally - they came somewhere to the size of a "pack" of 50,000 ... 200,000 lines
____________________________
* we are talking about a fairly universal tool for importing different price lists of different brands = in one case there are purchase and sale prices + classification by discount groups, somewhere only the base price, etc.