Updating multiple rows in sql server 2016

07-Sep-2019 10:08 by 2 Comments

Updating multiple rows in sql server 2016

Suppose there is such a list - let's call it likes - that records the number of likes for the objects that can be liked in a website.

If you are using Alibaba Cloud RDS, you may try the writing to combine the update and select statements into an SQL statement, and further reduce the network overhead and database overhead to improve the performance.As examples, I try to list a few scenarios where the enhanced syntax select from update applies.The first example is the distributed unique primary key generator.The gmt_xxx contains the record creation and update time without business meaning.The object_id is the primary key of the object that can be liked, which is equivalent to an external key, but the data consistency of association tables will be guaranteed by the application logic, and it will not be aware of the database; the count field records the number of likes of this object.So the back-end engineer writes such an SQL statement: update likes set gmt_modified = now(), count = count 1 where object_id = ? ; Then the liking API that provides the basic functionality can be worked out!

We noted that the two SQL statements are not in the same transaction; therefore, the count obtained via the select statement is not necessarily the result of the previous update statement.

The time used for network communication between the server and database for queries completely subject to indexes is many times that for queries in the database, plus begin and commit, which is meant to double the query time.

A transaction should only be opened in the cases where it is required to guarantee atomicity, such as updating multiple tables.

Sure enough, although it cannot be done with only one query, there is some way to implement it without opening a transaction.

That is to use a variable, put the update result in the variable, and then read out the variable in the same session. update likes set gmt_modified = now(), count = @cnt := count 1 where object_id = ?

When the problem was posted, there was no way to have it done via a single query in My SQL.