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:

CREATE TABLE Monsters
(FirstName VARCHAR(50) NULL
, LastName VARCHAR(50) NULL
, IsScary BIT NULL);

INSERT Monsters
(FirstName, LastName, IsScary)
VALUES ('Cookie', 'Monster', 0)
,('Frankenstein''s', 'Monster', 1)
,('Gila', 'Monster', 0)
,('Herman', 'Munster', 0);

SELECT * FROM Monsters;

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

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

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

UPDATE Monsters
SET LastName = FirstName,
LastName = NULL;

Uh oh.

Msg 264, Level 16, State 1, Line 20
The column name 'LastName' is specified more than once in the SET clause or column list of an INSERT. 
A column cannot be assigned more than one value in the same clause. 
Modify the clause to make sure that a column is updated only once. 
If this statement updates or inserts columns into a view, column aliasing can conceal the duplication in your code.

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?

UPDATE Monsters
SET LastName = FirstName,
FirstName = NULL;

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

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:

UPDATE Monsters
SET LastName = FirstName,
FirstName = LastName;

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.