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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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.
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:
|
1 2 3 |
UPDATE Monsters SET LastName = FirstName, LastName = NULL; |
Uh oh.
|
1 2 3 4 5 |
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?
|
1 2 3 |
UPDATE Monsters SET LastName = FirstName, FirstName = NULL; |
Will SQL Server update the FirstName to null, then update the LastName to be the null 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:
|
1 2 3 |
UPDATE Monsters SET LastName = FirstName, FirstName = LastName; |
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.
3 Comments. Leave new
Great to see you back again. I always enjoy your posts.
The last point is the one that I’ve seen trip people up most often. They want to run Update Col1 = newValue, Col2 = col1 + 10, and expect that Col2 will be newValue + 10.
Thanks, Greg. It’s good to be back at it. Yeah, sometimes it’s hard to convey there’s no sequence in an update statement — it all happens at once so columns reference each other’s updated values mid-statement.
[…] Doug Lane walks us through various scenarios with updates including the same column multiple times: […]