Introduction
If you need to update one row columns from another row columns in the same table following approach can be useful.
Background
To test queries and concept I am using SQL Server 2008 R2.
Queries
Create a sample table.
CREATE TABLE [dbo].[Employee](
[Title] [varchar](50) NULL,
[FirstName] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[DOB] [varchar](50) NULL,
[Datestarted] [varchar](50) NULL,
[EmployeeId] [int] IDENTITY(1,1) NOT NULL,
[Interest] [varchar](50) NULL,
[EmpAddress] [varchar](500) NULL
) ON [PRIMARY]
GO
Add some sample rows as follows.
I am just updating 2 columns. Of course you can update more in similar fashion. Here updating two columns of 1st row with 2nd row column values.
UPDATE n
SET n.EmpAddress = p.EmpAddress
,n.[Interest] = p.Interest
FROM [Employee] n inner join [Employee] p
ON n.employeeid = 1 AND p.employeeid = 2
GO
Select * from Employee
Please see next query if you wish to update only when value in the column is null.
Update column when value is null
I am going to use COALESCE. This is function similar to Case.
It returns the first nonnull expression among its arguments. Example:
select coalesce(Interest,'FUN') from [Employee] where EmployeeId = 5
Updating two columns of row 4th when they are null with column values of row 3rd.
Precise query can look like
UPDATE n
SET n.EmpAddress = coalesce(n.EmpAddress, p.EmpAddress)
,n.[Interest] = coalesce(n.Interest, p.Interest)
FROM [Employee] n inner join [Employee] p
ON n.employeeid = 4 AND p.employeeid = 3
GO
Select * from Employee
Summary
In this article I have suggested queries to update row with anther row and how you can use coalesce to restrict modification to null fields only. I hope you can use these queries for other purposes too.