Update and select query

Now, what if you want to update all the tuples in one or more columns of a table with unique corresponding values in another table. Okay, actually it will, but it would be a long tedious script when you are working with a few hundred or a few thousand rows of data. In this tutorial, we will explore three options that will update all the tuples in one or more columns with unique values that are stored in a separate table.

Remember, you will need to make sure that the columns you are updating are of the same or compatible datatypes as the source columns. Always use a test server and a test database for testing and learning. In my sample, I am using a test database named "AdventureWorks". For simplicity's sake and for keeping with a standard test database, we will be working with the AdventureWorks database.

We will, however, create a couple of new tables along with a new schema to work with. Afterwards, we will simply dump both tables as well as the schema.

If you so choose. If you do not have the AdventureWorks database installed already, you can get a backup BAK version for free. BAK" file. In the following query, we will create a schema named "Test" and two tables, "Test. Person" and "Test. We will also create a primary — foreign key constraint to link the two tables. Finally, we will populate the tables with data from the existing Person. Person and Person. Address tables provided in the Adventurworks database.

This would allow us to work with and manipulate the data without disturbing any tables that pre-exist in the database. In our test environment, we want to update all the rows in the "City" and "PostalCode" columns in the "Test.

Person" table with the correct and corresponding data from the "Test. PersonAddress" table. Since each tuple in each row is unique, we could use the standard form "update — set" method, but that would take too long since it will have to be done one row at a time. Using this method, we will update the values in the "City" and "PostalCode" columns of the customers table with the data held in the "City" and "PostalCode" columns of the "Test.

With the SET keyword, we specified which columns in the target table we want updated and set them to equal the values found in the source table, matching the columns by aliases. The subquery works efficiently, but it has its own limitations, as highlighted earlier. The overall performance of your database depends on the table data, the number of updates, table relationships, indexes , and statistics.

SQL Server execution plans are a gateway for query optimizer and query executions. Learn how to read, interpret and analyze execution plan data. Here are three key areas to focus your SQL Server performance tuning efforts to keep your database in peak condition. You may withdraw your consent at any time. Please visit our Privacy Statement for additional information. Tweet Share Share. City, e. Replace the select keyword with update.

Specify the table name or alias name that needs to be updated. PostCode , e. PersonName , PersonLastName. N 'Salvador' , N 'Williams' ,. N 'Lawrence' , N 'Brown' ,. N 'Gilbert' , N 'Jones' ,. N 'Ernest' , N 'Smith' ,. N 'Jorge' , N 'Johnson'. PersonId , PostCode , City. City ,. AddressList Addr. ON Per. ON Addr. PostCode ,. City ;. PostCode ;. SET Persons. Author Recent Posts. Esat Erkec. His current interests are in database administration and Business Intelligence.

You can find him on LinkedIn. View all posts by Esat Erkec. Latest posts by Esat Erkec see all. City , Per. PostCode , Per.