SQL update from one Table to another based on a ID match
While working with SQL database you might often require to update one table data based on values of a column value in another table. For this to be done successfully you might require a common id field or foreign key which will match values of both tables to update.
Here is the syntax to perform the task in SQL Server:
UPDATE table1
SET t1.Col = t2.Col
FROM
table1 t1
INNER JOIN table2 t2
ON t1.ID = t2.ID;
To demonstrated this let us assume we have following two tables:
Place
===========
PlaceId int,
name nvarchar(200),
ispublished bit
PlaceDetails
==============
PlaceDetailId int,
PlaceId int,
DetailsDesc nvarchar(max),
SeoTitle nvarchar(max),
SeoDescription nvarchar(max),
status bit
Case 1: Update status field of PlaceDetails from values of ispublished in Place table.
Solution:
UPDATE PlaceDetails
SET d.status = p.ispublished
FROM PlaceDetails d
INNER JOIN Place p
ON d.PlaceId = p.PlaceId;
Case 2: Update status field of PlaceDetails from values of ispublished in Place table only for the places that are published.
Solution: You require to add a where condition along with previous query for this purpose.
UPDATE PlaceDetails
SET d.status = p.ispublished
FROM PlaceDetails d
INNER JOIN Place p
ON d.PlaceId = p.PlaceId
WHERE p.ispublished='True';
Case 3: Update SeoTitle and SeoDescription field of PlaceDetails for a specific placetypes
Solution: This is a complex implementation of the query that allows you to update a table values based on a complex query result. For this purpose we consider some more tables.
The following query is used to generate SEO Titile and SEO Description information for all of the places of a category:
SELECT p.PlaceId, p.name, 'Book a room in ' + p.name +', '+ c.Name+' | addressschool.com',
p.name +' is a '+ t.Name+' in '+c.Name+', '+n.Name +' that is located in '+d.formatted_address
FROM Place AS p INNER JOIN
PlaceDetail AS d ON p.PlaceId = d.PlaceId INNER JOIN
City AS c ON p.CityId = c.Id inner join
PlaceType t on p.PlaceTypeId = t.Id INNER JOIN
Country AS n ON p.CountryId = n.Id
WHERE (p.PlaceTypeId = 1)
Now we use the above query as a inline view in the update statement to update SeoTitle and SeoDescription information in PlaceDetails table:
UPDATE
PlaceDetail
SET
PlaceDetail.name = SR.SeoTitle,
PlaceDetail.url = SR.SeoDescription
FROM
PlaceDetail pd
INNER JOIN
(SELECT p.PlaceId, p.name, 'Book a room in ' + p.name +', '+ c.Name+' | addressschool.com' as SeoTitle,
p.name +' is a '+ t.Name+' in '+c.Name+', '+n.Name +' that is located in '+d.formatted_address as SeoDescription
FROM Place AS p INNER JOIN
PlaceDetail AS d ON p.PlaceId = d.PlaceId INNER JOIN
City AS c ON p.CityId = c.Id inner join
PlaceType t on p.PlaceTypeId = t.Id INNER JOIN
Country AS n ON p.CountryId = n.Id
WHERE (p.PlaceTypeId = 1)) SR
ON
pd.PlaceId = SR.PlaceId;
Hope this will help you.
Any query and comments will be appreciated and answered.
