SQL Query : Update row columns with another row

How to update row columns with another row if column values are null in SQL

Update row column with another row column in same table SQL only if it is null.

Background

To test queries and concept I am using SQL SERVER 2008 R2.

Queries

If you need to update one row columns from another row columns in the same table following approach can be useful.

Create 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 2 columns of 1^st row with 2^nd 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 4^th when they are null with column values of row 3^rd.

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.

Tag: SQL, SQL SERVER, SQL Server 2008, COALESCE, Update column when value is null, How to update row with another row in SQL, update row with another row column in same table SQL, Update table column with data from other columns in same row, SQL UPDATE with sub-query that references the same table, SQL UPDATE from another row in the same table, Update column value based on other columns in same table, Update data in one table with data from another table, update when value is null sql, How can I update a value on a column only if that value is null, update a column value in case null, update a column value when null, SQL UPDATE, but only if the old value is null, Update columns if it is a null, Update only if null, IS NULL and IS NOT NULL, Update to 0 if NULL