Performance of Update query compared To Delete

I have two tables : Shop and Product

Table Shop
(id INT AUTO_INCREMENT,
shop_id INT,
PRIMARY KEY(id)
);

Table Product
(
product_id INT AUTO_INCREMENT,
p_name VARCHAR(100),
p_price INT,
shop_id INT,
PRIMARY KEY(product_id),
FOREIGN KEY(shop_id) REFERENCES Shop(id)
);

On a server using Node and mysql2 package for queries. On a client side, I'm displaying all Products that are related to specific Shop in a table. User can change Products, and when he is pressing Save, requests are being made, sending new data, and storing her. User can either change existing Products, or add new ones.

But i have concerns, how it will behave with a relatively big amount of products per one shop. Let's say there are 1000 of them. The data that was inserted - marked with the flag saved_in_db=false. Existing data, that was changed - changed=true.

Considered a few approaches :

On a server, filtering array of records received from a client, INSERT into db newly created, that are not stored yet. But to UPDATE existing Products, i need to create a bunch of UPDATE Products SET p_name=val_1 WHERE id = ? queries, and execute them at once.
To take all Products with the specified Shop_id, DELETE them, and INSERT a new bulk of data. Not making separation between already existing records, or changed. In this approach, i see two cons. First - sending constant amount of data from client to server. Second - running out of ids in DB. Because if there are 10 shops, with 1000 Products in each, and every user frequently updates records, every update, even if one new record was added, or changed, will increment id by around 1000.

Is it the only way, to update a certain amount of records in DB, executing a bunch of UPDATE queries one after another?

CodePudding user response：

You could INSERT...ON DUPLICATE KEY UPDATE.

INSERT INTO Products (product_id, p_name)
VALUES (123, 'newname1'), (456, 'newname2'), (789, 'newname3'), ...more...
ON DUPLICATE KEY UPDATE p_name = VALUES(p_name);

This does not change the primary key values, it only updates the columns you tell it to.

You must include the product id's in the INSERT VALUES, because that's how it detects that you're inserting a row that already exists in the table.