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

(

ID INT NOT NULL CONSTRAINT PK_custID PRIMARY KEY,

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,

ParentID INT CONSTRAINT FK_CustID REFERENCES CustomerMaster(ID)

)

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