“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.
[sendpulse-form id=”12968″]
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.
- We can update one table at a time for at least 1 column or a few columns.
- 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.
- We can use either INNER or OUTER JOIN (see examples later).
- 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:
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.
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
- Your Ultimate Guide to SQL JOIN: INNER JOIN – Part 1
- Your Ultimate Guide to SQL JOIN: OUTER JOIN – Part 2
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:
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.
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 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:
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.
Tags: sql join, sql server, sql server joins, sql update Last modified: September 26, 2022
