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
Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: