Click here to Skip to main content
13,593,234 members
Click here to Skip to main content
Add your own
alternative version


4 bookmarked
Posted 17 Aug 2012
Licenced CPOL

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

, 17 Aug 2012
Rate this:
Please Sign up or sign in to vote.
Update row column with another row column in same table SQL only if it is null.


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


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


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

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.

SET n.EmpAddress = p.EmpAddress
,n.[Interest] = p.Interest
FROM [Employee] n inner join [Employee] p
ON n.employeeid = 1 AND p.employeeid = 2
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

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
Select * from Employee


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.


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


About the Author

United States United States
Manoj Kumar

AWS Certified Solutions Architect (Web development, Serverless, DevOps, Data)
MSSE - Cloud and Mobile Computing, San Jose State University

A wide range of experience in resolving complex business problems.

* Cloud Technologies: EC2, S3, DynamoDB & RDS databases, Lambda serverless architecture, Microservices architecture, API Gateway, Cloud Front CDN, Linux/Windows systems administration, CloudFormation, DevOps, Docker, CICD, Node.js, Python, Java and other open source technologies. Familiarity with OpenStack.
* Web Technologies: HTML5, Node.Js, MEAN Stack, AngularJS, ASP.Net Core, MVC5, CSS3, jQuery, Bootstrap, MongoDB, JavaScript, JSON, AJAX.
* Data: Experience in database architecture, Big Data, Machine Learning, BI, Data Analytics, No-SQL databases, ETL.
* Mobile: IOS/Android app development

He lives with his wife Supriya and daughter Tisya in Bay Area.

You may also be interested in...


Comments and Discussions

QuestionHow to update a row with another row if column values are null in SQL Pin
Member 1330587712-Jul-17 0:08
memberMember 1330587712-Jul-17 0:08 
Suggestiongood one Pin
Rajesh Varma Buddaraju22-May-14 0:25
memberRajesh Varma Buddaraju22-May-14 0:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180618.1 | Last Updated 17 Aug 2012
Article Copyright 2012 by ManojKumar19
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid