UPDATE Query with JOIN in SQL-Server


I know all of you are aware with UPDATE statement of SQL-Server but may be some of you don’t know the power of JOIN in UPDATE statement. Herewith, I am going to describe UPDATE with JOIN. Sometime in real world, we have requirement to update some fields of one table depend upon another table. To cater this need we may use iteration tool like “WHILE”, sometime which is the cause of low performance so in that case you can go for UPDATE with JOIN

Well, let start our practical session by creating one table.

CREATE TABLE CustomerMaster



      CompanyName VARCHAR(20) NOT NULL,

      Email VARCHAR(50) NOT NULL



And now we will enter some records in above table.

INSERT INTO CustomerMaster VALUES (1,‘PCI INC.’,‘info@pci.com’)

INSERT INTO CustomerMaster VALUES (2,‘GW INC.’,‘info@gw.com’)


Now, we will create another table and along with some INSERT script to enter the records.

CREATE TABLE CustomerContact


      CustomerContactName VARCHAR(15) NOT NULL,

      Email VARCHAR(50) NULL,




INSERT INTO CustomerContact VALUES(‘Ritesh’,‘R@pci.com’,1)

INSERT INTO CustomerContact VALUES(‘Alka’,NULL,1)

INSERT INTO CustomerContact VALUES(‘Avi’,NULL,2)


Now, you can observe that our record number 2 and three doesn’t have their own email address so now we may wish to update those email addresses from its company’s email addresses (parent’s). below will be the query for same.

UPDATE CustomerContact SET EMAIL=

(SELECT Email FROM CustomerMaster cm where cm.ID=CustomerContact.parentID) where email is null


In above case, our NULL value in email field of customerContact table will be populated by its corresponding records from parent table.

Reference: Ritesh Shah


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: