How to UPDATE from a SELECT statement in SQL Server
In this article, we will learn different strategies that are utilized to update the data in a table with the data of other tables. The “UPDATE from SELECT” query structure is the primary strategy for performing these updates.
An UPDATE query is utilized to change a current row or rows in the database. UPDATE queries can change all tables rows, or we can limit the update explanation influences for the certain rows with the assistance of the WHERE statement. Generally, we utilize consistent values to change the data, for example, the accompanying structures.
The full update proclamation is utilized to change the entire table data with a similar worth.
UPDATE table SET col1 = constant_value1 , col2 = constant_value2 , colN = constant_valueN |
The conditional update statement is used to change the data that satisfies the WHERE condition.
UPDATE table SET col1 = constant_value1 , col2 = constant_value2 , colN = constant_valueN WHERE col = val |
However, for different scenarios, this consistent worth utilization type can’t be sufficient for us, and we have to utilize other tables’ data so as to update our table. This sort of update explanations is a bit muddled than the standard structures. In the accompanying areas, we will learn how to write this kind of update query with different strategies, however at first, we need to prepare our example data. So we should do this.
Preparing the sample data
With the assistance of the accompanying query, we will create Persons and AddressList tables and populate them with some manufactured data. These two tables have a relationship through the PersonId segment, implying that, in these two tables, the PersonId section esteem represents a similar person.
CREATE TABLE dbo.Persons ( PersonId INT PRIMARY KEY IDENTITY(1, 1) NOT NULL, PersonName VARCHAR(100) NULL, PersonLastName VARCHAR(100) NULL, PersonPostCode VARCHAR(100) NULL, PersonCityName VARCHAR(100) NULL) GO CREATE TABLE AddressList( [AddressId] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL, [PersonId] [int] NULL, [PostCode] [varchar](100) NULL, [City] [varchar](100) NULL) GO INSERT INTO Persons (PersonName, PersonLastName ) VALUES (N‘Salvador’, N‘Williams’), (N‘Lawrence’, N‘Brown’), ( N‘Gilbert’, N‘Jones’), ( N‘Ernest’, N‘Smith’), ( N‘Jorge’, N‘Johnson’) GO INSERT INTO AddressList (PersonId, PostCode, City) VALUES (1, N‘07145’, N‘Philadelphia’), (2, N‘68443’, N‘New York’), (3, N‘50675’, N‘Phoenix’), (4, N‘96573’, N‘Chicago’) SELECT * FROM Persons SELECT * FROM AddressList |
UPDATE from SELECT: Join Method
In this strategy, the table to be updated will be gotten together with the reference (secondary) table that contains new row values. So that, we can get to the coordinated data of the reference table dependent on the predefined join type. Finally, the sections to be updated can be coordinated with referenced segments and the update process changes these segment values.
In the accompanying model, we will update the PersonCityName and PersonPostCode segments data with the City and PostCode segments data of the AdressList table.
UPDATE PerSETPer.PersonCityName=Addr.City,Per.PersonPostCode=Addr.PostCodeFROM Persons PerINNER JOINAddressList AddrON Per.PersonId = Addr.PersonId
After the execution of the update from a select query the output of the Persons table will be as shown below;
SELECT * FROM Persons |
Let’s try to understand the above code:
We composed the table name, which will be updated after the UPDATE proclamation. After the SET keyword, we indicated the segment names to be updated, and furthermore, we coordinated them with the referenced table sections. After the FROM proviso, we retyped the table name, which will be updated. After the INNER JOIN condition, we indicated the referenced table and joined it to the table to be updated. In addition to this, we can indicate a WHERE condition and filter any sections of the referenced or updated table. We can likewise rewrite the query by utilizing monikers for tables.
UPDATE Per SET Per.PersonCityName=Addr.City, Per.PersonPostCode=Addr.PostCode FROM Persons Per INNER JOIN AddressList Addr ON Per.PersonId = Addr.PersonId |
Performance Tip:
Indexes are very helpful database objects to improve query performance in SQL Server. Particularly, if we are working on the performance of the update query, we should take into account of this probability. The following execution plan illustrates an execution plan of the previous query. The only difference is that this query updated the 3.000.000 rows of the Persons table. This query was completed within 68 seconds.
We added a non-clustered index on Persons table before to update and the added index involves the PersonCityName and PersonPostCode columns as the index key.
The following execution plan is demonstrating an execution plan of the same query, but this query was completed within 130 seconds because of the added index, unlike the first one.
The Index Update and Sort operators expend 74% expense of the execution plan. We have seen this undeniable performance difference between a similar query as a result of index use on the updated segments. As a result, if the updated segments are being utilized by the indexes, this way, for instance, the query performance may be influenced contrarily. In particular, we ought to consider this problem on the off chance that we will update a large number of rows. To overcome this issue, we can debilitate or remove the index before executing the update query.
Then again, a warning sign is seen on the Sort operator, and it demonstrates something turns out poorly for this operator. At the point when we hover the mouse over this operator, we can see the warning subtleties.
During the execution of the query, the query optimizer figures a required memory utilization for the query dependent on the assessed row numbers and row size. However, this utilization assessment can not be right for a variety of reasons, and if the query requires more memory than the assessment, it utilizes the tempdb data. This component is known as a tempdb spill and causes performance misfortune. The reason for this: the memory in every case faster than the tempdb database in light of the fact that the tempdb database utilizes the circle resources.
UPDATE from SELECT: The MERGE statement
The MERGE statement is used to manipulate (INSERT, UPDATE, DELETE) a target table by referencing a source table for the matched and unmatched rows. The MERGE statement can be very useful for synchronizing the table from any source table.
Now, if we go back to our position, the MERGE statement can be used as an alternative method for updating data in a table with those in another table. In this method, the reference table can be thought of as a source table and the target table will be the table to be updated. The following query can be an example of this usage method.
MERGE Persons AS Per USING(SELECT * FROM AddressList) AS Addr ON Addr.PersonID=Per.PersonID WHEN MATCHED THEN UPDATE SET Per.PersonPostCode=Addr.PostCode , Per.PersonCityName = Addr.City; SELECT * FROM Persons |
Now let’s tackle the previous update from a select query line by line.
MERGE Persons AS Per |
We have typed the Persons table after the MERGE statement because it is our target table, which we want to update, and we gave Per alias to it in order to use the rest of the query.
USING(SELECT * FROM AddressList) AS Addr |
After the USING statement, we have specified the source table.
ON Addr.PersonID=Per.PersonID |
With the help of this syntax, the join condition is defined between the target and source table.
WHEN MATCHED THEN UPDATE SET Per.PersonPostCode=Addr.PostCode; |
In this last line of the query, we chose the manipulation method for the matched rows. Individually for this query, we have selected the UPDATE method for the matched rows of the target table. Finally, we added the semicolon (;) sign because the MERGE statements must end with the semicolon signs.
UPDATE from SELECT: Subquery Method
A subquery is an interior query that can be utilized within the DML (SELECT, INSERT, UPDATE and DELETE) articulations. The major characteristic of the subquery is, they must be executed with the external query.
The subquery strategy is the very essential and simple technique to update existing data from other tables’ data. The observable difference in this strategy is, it may be an advantageous method to update one segment for the tables that have few the rows. Presently we will execute the accompanying query and afterward will break down it.
UPDATE Persons SET Persons.PersonCityName=(SELECT AddressList.PostCode FROM AddressList WHERE AddressList.PersonId = Persons.PersonId) |
After the execution of the update from a select statement the output of the table will be as below;
SELECT * FROM Persons |
As we can see, the PersonCityName column data of the Persons table have been updated with the City column data of the AddressList table for the matched records for the PersonId column. Regarding this method, we should underline the following significant points.
- If the subquery could not find any matched row, the updated value will be changed to NULL
- If the subquery finds more than one matched row, the update query will return an error, as shown below:
- Many times the subquery update method may not offer satisfying performance
Conclusion
In this article, we learned to update the data in a table with the data where they are contained in other tables. The query structure, “UPDATE from SELECT” can be utilized to perform this sort of data update scenario. Additionally, we can utilize alternative MERGE articulation and subquery strategies.
