How to Update from a Select statement in SQL Server.

This code example uses SQL Server 2014 Standard

There are a number of ways you can update a table in SQL Server, in this blog I will show you four ways to do this.

First off let's create our test tables and then add some test data.

CREATE TABLE dbo.PersonNameAndAddress
( PersonId       INT
  PRIMARY KEY IDENTITY(1, 1) NOT NULL, 
  FirstName     NVARCHAR(100) NULL, 
  LastName NVARCHAR(100) NULL, 
  CityName NVARCHAR(100) NULL,
  PostCode NVARCHAR(100) NULL, 
 )
 
GO

CREATE TABLE  PersonAddress(
  AddressId INT  PRIMARY KEY IDENTITY(1,1) NOT NULL,
  PersonId INT NULL,
  PostCode NVARCHAR(100) NULL,
  CityName NVARCHAR(100) NULL)
 
GO

INSERT INTO PersonNameAndAddress
(FirstName, LastName )
VALUES
(N'Elvis', N'Presley'),
(N'Cliff', N'Richard'),
(N'Michael', N'Jackson'),
(N'Kylie', N'Minogue'),
(N'Elton', N'John'),
(N'Robbie', N'Williams'),
(N'Ed', N'Sheeran'),
(N'Calvin', N'Harris'),
(N'Justin', N'Bieber'),
(N'Rod', N'Stewart')

INSERT INTO PersonAddress
(PersonId, PostCode, CityName)
VALUES
(1, N'AB', N'Aberdeen'),
(2, N'Al', N'St Albans'),
(3, N'B', N'Birmingham'),
(4, N'BB', N'Blackburn'),
(5, N'BD', N'Bradford'),
(6, N'BH', N'Bournemouth'),
(7, N'BL', N'Bolton'),
(8, N'BN', N'Brighton'),
(9, N'BR', N'Bromley'),
(10, N'BS', N'Bristol')

If we now run the following SQL select statement, we should see the following results:

Multiple SQL Select Statements

As you can see we have now created our tables and populated them with test data.

Update statement on one table.

If you now run the SQL update table statement, you will see we have added 'Test Update' to the postcode column in the PersonNameAndAdress table where the PersonId is equal to one.

--Simple SQL update using just one table
update p
set p.postcode = 'Test Update'
from PersonNameAndAddress p
where p.personId = 1
SQL update single table

SQL update using join method.

The next update from select will use the join method and this time we will use multiple columns and multiple tables.

--SQL update using join method

update p
set 
p.PostCode =  addr.PostCode,
p.CityName =  addr.CityName
from PersonNameAndAddress p
inner join 
PersonAddress addr
on p.PersonId = addr.PersonId

If we again run our select statement, you should see the following:

SQL update multiple columns

SQL update using merge method.

Our next update SQL code will be to use the merge statement, I showed how this can be used in a more complicated example when downloading Ean data

The code for using merge is below:

--SQL update using merge method
Merge PersonNameAndAddress as p
USING(SELECT * FROM PersonAddress) AS addr
ON addr.PersonId=p.PersonId
WHEN MATCHED THEN
UPDATE SET
p.PostCode =  addr.PostCode,
p.CityName =  addr.CityName;

Again running the above code will produce the same results as above.

SQL update using subquery method.

The final way of updating data in this post is to use SQL subquery method, the code for this type of update query is below.

update PersonNameAndAddress
set 
PersonNameAndAddress.CityName = (SELECT PersonAddress.PostCode FROM PersonAddress WHERE PersonAddress.PersonId=PersonNameAndAddress.PersonID)

And one more time running the above will display the same results as all the other examples above.

That's it for this blog post, I hope you find the blog helpful and thanks for reading.