SQL update from one Table to another based on a ID match

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.