Updates With Joins

Kevin Feasel

2016-05-23

Syntax

James Anderson shows a case in which an update with a join clause can lead to undesirable results:

That’s right; SQL Server will just pick any child. It will not update the parent row for each child. When the MERGE statement runs into this problem, it raises an error and rolls back the UPDATE but our query will silently pick any value and move on. We don’t want to update data like this, we want our query to dictate the logic explicitly. This will require some changes to our query.

I prefer to write these types of updates with the use of a CTE. This allows us to easily highlight the SELECT in the CTE and execute it to see which rows will be updated (@variables can cause problems here though). Adding a COUNT can help to identify the problem in the previous query.

Highlight and execute the code inside the CTE below to check for duplicates. If this returns 0 rows then you are good to remove the COUNT, GROUP BY and HAVING then add the name columns before executing the whole statement.

When writing T-SQL updates with joins, it’s important to consider whether the grain changes, and if that change can make a difference in your update set.

Related Posts

What’s In SQL Server 2019 CTP 2.0?

Aaron Bertrand gives us the highlights: Certificate Management in Config Manager View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance. Built-in data classification A new ADD SENSITIVITY CLASSIFICATION statement helps you identify and […]

Read More

Multiple Mentions Of A Column In An UPDATE Statement

Doug Lane walks us through various scenarios with updates including the same column multiple times: An application developer came to me with this question recently: “Can I use the same column twice in a SQL UPDATE statement?” Yes and no. It depends on what you mean by “use”. Read on to see what Doug means.

Read More

Categories

May 2016
MTWTFSS
« Apr Jun »
 1
2345678
9101112131415
16171819202122
23242526272829
3031