Using the Same Column Twice in a SQL UPDATE Statement

Coding, T-SQL

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”.

Let’s take an example where we’re updating a table called Monsters. Here’s the code to build it:

We have four rows of data about monsters — first and last names, and whether they are the scary kind of monster or not.

Contents of the Monsters table

The kids on Mockingbird Lane find that last entry debatable.

(Hey, congratulations if you know who Herman Munster is!)

Let’s try updating the LastName column twice by assigning it conflicting values:

Uh oh.

Sometimes, Management Studio’s error messages are vague and unhelpful, but this one is pleasantly straightforward: Limit one change per column. The UPDATE statement can’t pick a winner between the two instructions because they execute at the same time. It’s like changing the tire on a car and telling it to use two different tires on the same wheel.

What if we use the column twice but in two different ways: Once as a reference, the other as the column to update?

Will SQL Server update the FirstName to null, then update the LastName to be the null FirstName?

Monsters table with first names = NULL and LastName = FirstName

Remember, it’s the doctor’s name that’s Frankenstein, not the monster’s.

Nope. SQL Server will run this all at once, using a snapshot of existing data as its source.

The order of columns in an UPDATE statement doesn’t matter. The first column referenced in the statement doesn’t “go first”. The NULL-ing of FirstName doesn’t happen before or after we set LastName to FirstName.

Still not sure about all that? Try this statement:

Monsters table with name columns flipped

The first row is making me hungry.

If SQL Server had to pick an order to update the columns, this flipping of data would fail. Instead, it sets the LastName to whatever the FirstName happens to be before the update runs.

You can reference a column in an UPDATE statement as many times as you like, but you can only set its value once. If you need to change the value a second time, SQL Server requires a second UPDATE statement.

Previous Post
T-SQL Tuesday #96: Three Pivot Points

Related Posts

No results found.

3 Comments. Leave new