Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

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

5.00/5 (4 votes)
17 Aug 2012CPOL 78.9K  
Update row column with another row column in same table SQL only if it is null.

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.

SQL
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.

Image 1

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.

SQL
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

Image 2

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:

SQL
select coalesce(Interest,'FUN') from [Employee] where EmployeeId = 5 

Image 3

Updating two columns of row 4th when they are null with column values of row 3rd.

Precise query can look like

SQL
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

Image 4

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)