5 No-Hassle Tips to Use SQL UPDATE Statement with JOIN

“Oops! My bad.” How many times did you say this after an SQL UPDATE had gone wrong? The thing is, if you aren’t careful, a table update can have serious consequences in the form of the DELETE statement. It could become even worse if you complicate it by using UPDATE with JOIN. That’s why you need to think it over before hitting Execute or pressing CTRL-E.

So, today you will learn how to code your SQL UPDATE with JOIN without hassles and never say “Oops! My bad” again.

But before we get to practice, we start with the syntax. It will also make our newbies feel at home about SQL Server UPDATE with JOIN. Then, we will prepare some data and a few examples. And finally, examine the safety tips. For those who’d prefer skipping the theory part – just move to the Using Update Command with Join Safely (5 Tips) section at once.

CodingSight - 5 No-Hassle Tips to Run SQL UPDATE with JOIN

SQL UPDATE JOIN Syntax 

UPDATE table1
SET column1 = <expression1 | value1> [, column2 = <expression2 | value2>, <columnN = expression3 | value3>]
FROM table1
[INNER | OUTER LEFT | OUTER RIGHT] JOIN table2 on table1.keycolumn = table2.keycolumn
[WHERE condition]

We need to itemize a few points from this.

  1. We can update one table at a time for at least 1 column or a few columns.
  2. We need the FROM clause to add a JOIN. The object in the FROM clause may or may not be the same as the object being updated.
  3. We can use either INNER or OUTER JOIN (see examples later).
  4. We can update only a subset of the data using the WHERE clause.

Before we have our examples, let’s prepare the data.

Our Test Data

For the love of movies, let’s create a database of movie titles with user ratings.

CREATE DATABASE [Movies]
GO

USE [Movies]
GO

CREATE TABLE [dbo].[Titles](
	[TitleID] [int] IDENTITY(1,1) NOT NULL,
	[Title] [varchar](50) NOT NULL,
	[ReleaseDate] [date] NOT NULL,
	[OverallUserRating] [varchar](10) NULL,
 CONSTRAINT [PK_Titles] PRIMARY KEY CLUSTERED 
(
	[TitleID] ASC
))
GO

CREATE TABLE [dbo].[UserRatings](
	[UserRatingID] [int] IDENTITY(1,1) NOT NULL,
	[TitleID] [int] NOT NULL,
	[User] [varchar](50) NOT NULL,
	[Rating] [tinyint] NOT NULL,
 CONSTRAINT [PK_UserRatings] PRIMARY KEY CLUSTERED 
(
	[UserRatingID] ASC
))
GO

ALTER TABLE [dbo].[UserRatings]  WITH CHECK ADD  CONSTRAINT [FK_UserRatings_Titles] FOREIGN KEY([TitleID])
REFERENCES [dbo].[Titles] ([TitleID])
GO

ALTER TABLE [dbo].[UserRatings] CHECK CONSTRAINT [FK_UserRatings_Titles]
GO

ALTER TABLE [dbo].[UserRatings]  WITH CHECK ADD  CONSTRAINT [CK_UserRatings_Rating] CHECK  (([Rating]>=(1) AND [Rating]<=(5)))
GO

ALTER TABLE [dbo].[UserRatings] CHECK CONSTRAINT [CK_UserRatings_Rating]
GO

Now that we have the database and tables, let’s insert some data:

INSERT INTO Titles
(Title, ReleaseDate)
VALUES 
('The Avengers', '05/04/2012'),
('Avengers: Age of Ultron','5/1/2015'),
('Avengers: Infinity War','4/27/2018'),
('Avengers: Endgame','4/26/2019'),
('Captain America: Civil War','5/6/2016')
GO

INSERT INTO UserRatings(TitleID, [User], Rating) 
VALUES 
(1,'Natasha',5),
(1,'Bruce',3),
(1,'Tony',4),
(1,'Bucky',5),
(2,'Steve',4),
(2,'Wanda',3),
(2,'Pietro',2),
(2,'Clint',5),
(3,'Hope',5),
(3,'Sam',5),
(3,'Nick',3),
(3,'James',5),
(4,'Scott',5),
(4,'Wong',5),
(4,'Peter',5),
(4,'Carol',4),
(4,'Shuri',5)
GO

SQL Server UPDATE with JOIN Example  

We will examine different examples having the same goal of updating the OverallUserRating in the Titles table. Ratings can be 1 to 5. OverallUserRating is the average of all ratings for a movie title.

Here’s the initial state of the table:

Initial OverallUserRating value is NULL for all records in the Titles table.
Figure 1. Initial OverallUserRating value is NULL for all records in the Titles table.

UPDATE LEFT JOIN Example

-- SQL UPDATE with LEFT OUTER JOIN
SELECT
 a.TitleID
,CAST(CAST(AVG(CAST(b.Rating AS DECIMAL(3,2))) AS DECIMAL(3,2)) AS varchar(9)) AS AverageRating
INTO #ComputedRatings
FROM titles a
INNER JOIN UserRatings b ON a.TitleID = b.TitleID
GROUP BY a.TitleID

-- mark 'No Rating' if there are no existing ratings
UPDATE Titles
SET OverallUserRating = ISNULL(b.AverageRating,'No Rating') 
FROM Titles a
LEFT JOIN #ComputedRatings b ON a.TitleID = b.TitleID

The first SELECT statement computes the average rating per movie title based on the UserRatings table. The result is dumped to a temporary table called #ComputedRatings. As we use INNER JOIN, the movie titles without ratings are discarded.

In the UPDATE statement, the Titles table was updated using a LEFT JOIN from the #ComputedRatings temporary table. If the average rating is null, the value becomes No Rating. In our sample, Captain America: Civil War has no user ratings yet – see Figure 2.

The result of SQL UPDATE with JOIN that uses LEFT JOIN.
Figure 2. The result of SQL UPDATE with JOIN that uses LEFT JOIN.

SQL UPDATE INNER JOIN Example 

Here’s how it goes:

-- SQL UPDATE with INNER JOIN
SELECT
 a.TitleID
,ISNULL(CAST(CAST(AVG(CAST(b.Rating AS DECIMAL(3,2))) AS DECIMAL(3,2)) AS varchar(9)),'No Rating')  AS AverageRating
INTO #ComputedRatings
FROM titles a
LEFT JOIN UserRatings b ON a.TitleID = b.TitleID
GROUP BY a.TitleID


UPDATE Titles
SET OverallUserRating = b.AverageRating
FROM Titles a
INNER JOIN #ComputedRatings b ON a.TitleID = b.TitleID

When you run the above code, the result will be the same as in Figure 2. But what’s the difference between the two codes?

  • The first SELECT statement considers the NULL user rating unlike our LEFT JOIN example earlier. It does not discard the movie title with no user ratings. So, this time, the No Rating value for Captain America: Civil War is considered already.
  • An INNER JOIN with the direct assignment of the AverageRating value is more appropriate since all TitleIDs are accounted for.

Now, instead of a temporary table, common table expression (CTE) can also be used. Here’s the modified code:

-- SQL UPDATE with JOIN using INNER JOIN from a CTE
;WITH ComputedRatings AS
(SELECT
 a.TitleID
,ISNULL(CAST(CAST(AVG(CAST(b.Rating AS DECIMAL(3,2))) AS DECIMAL(3,2)) AS varchar(9)),'No Rating')  AS AverageRating
FROM Titles a
LEFT JOIN UserRatings b ON a.TitleID = b.TitleID
GROUP BY a.TitleID)
UPDATE Titles
SET OverallUserRating = b.AverageRating
FROM Titles t
INNER JOIN ComputedRatings cr ON t.TitleID = cr.TitleID

More Information

Using Update Command with Join Safely (5 Tips) 

Safety refers to updating intended records. Also, it is about NOT touching the records we don’t intend to update. Here, we’ll deal with 5 scenarios:

View the Records First with the SELECT Statement

This tip is perfect for a few records. So, before you affect the records for updating, try this:

To view the records to update, comment out the UPDATE and SET clauses and replace them with a SELECT.
Figure 3. To view the records to update, comment out the UPDATE and SET clauses and replace them with a SELECT.

That’s easy to do. And if it looks good, uncomment the UPDATE and SET clauses. Mark the SELECT clause as a comment. Then, you’re good to go. This way you minimize damage control because of being proactive.

Do a Trial Run Using Temporary Tables

Feeling unsure if an error may occur? Then try to dump the table records you wish to update into a temporary table. After that, do a trial run from there.

Replacing the original table with a temporary table to do a trial run for the update.
Figure 4. Replacing the original table with a temporary table to do a trial run for the update.

Are there no runtime errors? Do the results look good? Then, replace the temporary table with the original table. This way, you know there will be no runtime errors along the way.

Also, notice that temporary tables are one of the ways to store the copies of original tables. You can also use memory-optimized tables or a database backup. With the database backups, you have more freedom to play with records you need to update. But the downside of this is storage space.

More Information

Try to Add the OUTPUT Clause into UPDATE

Do you want to get back in time before you run the update? If you are that skeptical, you can use the OUTPUT clause and view the past and the present. In the example below, a table variable serves to dump the previous and present values after the update. You can then SELECT the table variable to see the results:

DECLARE @tvTitles AS TABLE(TitleID INT NOT NULL,
                           OldOverallRatings VARCHAR(10) NULL,
			    NewOverAllRatings varchar(10) NOT NULL)

;WITH ComputedRatings AS
(
	SELECT
	a.TitleID
	,ISNULL(CAST(CAST(AVG(CAST(b.Rating AS DECIMAL(3,2))) AS DECIMAL(3,2)) AS varchar(9)),'No Rating')  AS AverageRating
	FROM titles a
	LEFT JOIN UserRatings b ON a.TitleID = b.TitleID
	GROUP BY a.TitleID
)
UPDATE #tmpTitles
SET OverallUserRating = cr.AverageRating
OUTPUT INSERTED.TitleID, DELETED.OverallUserRating, INSERTED.OverallUserRating
INTO @tvTitles
FROM #tmpTitles t
INNER JOIN ComputedRatings cr ON t.TitleID = cr.TitleID

A few points to note about this code:

  • The table variable works as a container of the previous and present values.
  • The usual update with CTE is in order. We still use the temporary table to play safe.
  • The OUTPUT clause applies to dump the previous and present values into the table variable. INSERTED contains new values, while DELETED has old values.

If you issue a SELECT from the table variable, here’s what you may expect:

The OUTPUT clause in the UPDATE statement containing the old and new values after the UPDATE.
Figure 5. The OUTPUT clause in the UPDATE statement containing the old and new values after the UPDATE.

The result is like Figure 3, but it looks into the future. This one looks into the past. If it’s different, something has gone wrong in between.

The good news is you can use old values in the table variable to restore it to its previous state. But if it’s the same, then again, you’re good to go. You can mark the OUTPUT clause as a comment or remove it, and then replace the temporary table with the original table.

More Information

Use TRY…CATCH to Handle Future Errors

The previous 3 tips are helpful when you are crafting and then testing your code. But we all know that we can’t anticipate everything. Thus, we need to add more safety nets to the code.

Speaking of safety nets, T-SQL has the TRY…CATCH error-handling blocks like C# and C++.

Let’s have a look at the modified code from the previous example:

BEGIN TRY						  
  ;WITH ComputedRatings AS
  (
    SELECT
     a.TitleID
    ,ISNULL(CAST(CAST(AVG(CAST(b.Rating AS DECIMAL(3,2))) AS DECIMAL(3,2)) AS varchar(20)),
            'No User Ratings Yet') AS AverageRating
    FROM titles a
    LEFT JOIN UserRatings b ON a.TitleID = b.TitleID
    GROUP BY a.TitleID
  )
  UPDATE Titles
  SET OverallUserRating = cr.AverageRating
  FROM Titles t
  INNER JOIN ComputedRatings cr ON t.TitleID = cr.TitleID

END TRY
BEGIN CATCH
 SELECT  
  ERROR_NUMBER() AS ErrorNumber  
 ,ERROR_SEVERITY() AS ErrorSeverity  
 ,ERROR_STATE() AS ErrorState  
 ,ERROR_PROCEDURE() AS ErrorProcedure  
 ,ERROR_LINE() AS ErrorLine  
 ,ERROR_MESSAGE() AS ErrorMessage;  

END CATCH

I changed the code above to force the string truncation error. The OverallUserRating column in the Titles table can accommodate up to 10 characters only. In the CTE, we changed it to 20 characters. That won’t fit. The CATCH block will take over the moment of the error occurrence and provide the error information.

Here’s the result:

Result of the TRY…CATCH block for SQL UPDATE
Figure 6. Result of the TRY…CATCH block for SQL UPDATE

We triggered the error. If you need to catch unforeseen errors during runtime, this is one way to handle it.

More Information

Use Transaction Handling

Finally, transactions. It ensures to restore everything to its previous state before the error occurred, including UPDATE with JOIN and any other DML statements. This is a good addition to Tip #4 above.

Let’s change the code again to include transactions:

BEGIN TRANSACTION

BEGIN TRY						  
  ;WITH ComputedRatings AS
  (
    SELECT
     a.TitleID
    ,ISNULL(CAST(CAST(AVG(CAST(b.Rating AS DECIMAL(3,2))) AS DECIMAL(3,2)) AS varchar(20)),
            'No User Ratings Yet') AS AverageRating
    FROM titles a
    LEFT JOIN UserRatings b ON a.TitleID = b.TitleID
    GROUP BY a.TitleID
  )
  UPDATE Titles
  SET OverallUserRating = cr.AverageRating
  FROM Titles t
  INNER JOIN ComputedRatings cr ON t.TitleID = cr.TitleID

  COMMIT TRANSACTION
END TRY
BEGIN CATCH
 SELECT  
  ERROR_NUMBER() AS ErrorNumber  
 ,ERROR_SEVERITY() AS ErrorSeverity  
 ,ERROR_STATE() AS ErrorState  
 ,ERROR_PROCEDURE() AS ErrorProcedure  
 ,ERROR_LINE() AS ErrorLine  
 ,ERROR_MESSAGE() AS ErrorMessage;  

 ROLLBACK TRANSACTION
END CATCH

It is the same as in the previous example except for transactions. Thus, it will force a string truncation error. It won’t go past COMMIT TRANSACTION, but rather in the CATCH block with the ROLLBACK TRANSACTION to revert the values to their previous states.

This is the way if we want to play safe with updates, inserts, and deletes.

Note: You can design any query visually in a diagram using the Query Builder feature of dbForge Studio for SQL Server.

More Information

Conclusion

You’ve seen the syntax of SQL UPDATE with JOIN. Examples and 5 no-hassle tips enlightened you further. The requirements may differ from what the examples present, but you got the point. You can still make mistakes. However, it is possible to reduce them to near zero.

Why not apply these ideas to your situation?

If this post was helpful, feel free to spread the word on your favorite social media platforms. And if you want to add some great ideas, you are welcome to the Comments section.

Edwin Sanchez

Edwin Sanchez

Software developer and project manager with a total of 20+ years of software development. His most recent technology preferences include C#, SQL Server BI Stack, Power BI, and Sharepoint. Edwin combines his technical knowledge with his most recent content writing skills to help new breed of technology enthusiasts.

Leave a Reply

Your email address will not be published. Required fields are marked *