How to update or delete using JOINS

Problem: How to update or delete records in table based on another table values.

Solution: JOIN

Joins are very helpful when comparing records in between tables, based on the need we can use an INNER, LEFT or RIGHT JOIN to address the query.

For More about joins please go through this article. JOINS

Example:

In this example I would like to show you a simple one to one update and delete when there is match present in both Tables A, B. Let’s see the below code and update a column in table B with column in table A. Similarly, I am going to delete matching rows in table B when there is a match in Col2 between both the tables.

SELECT [Col1]

      ,[Col2]

      ,[Col3]

  FROM [dbo].[TableB]

  SELECT  [Col1]

      ,[Col2]

      ,[Col3]

  FROM [dbo].[TableA]

See the below picture for tables data on each table.

  -- Updating records from Table B which are present in Table A.

  UPDATE b

  SET b.col1= a.col1

  FROM TableB b

  INNER JOIN TableA a

  ON a.Col2=b.Col2

In the below screenshot col1 gets updated to Zone5 based on the join conditions.

  -- Deleting records from Table B which are present in Table A.

  DELETE b

  FROM TableB b

  INNER JOIN TableA a

  ON a.Col2=b.Col2