Is the Order of Columns in the SET Clause Important in SQL Server?

The answer is NO. The order of columns in the SET clause of the UPDATE statement is NOT important. You probably already noticed from the previous tutorial. There is a BIG DIFFERENCE among SQL Server, MySQL and Oracle on update multiple columns with previous values:

• SQL Server provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important
• MySQL provides you the updated values on columns names used in new value expressions. So the order of columns in the SET clause is important.
• Oracle provides you the existing values from the database on columns names used in new value expressions. So the order of columns in the SET clause is NOT important

Here is a good tutorial exercise:

SELECT * FROM fyi_links
-- Check the old values
SELECT * FROM fyi_links WHERE url = 'dev.fyicenter.com'
GO
id      url                     notes   counts  created
101     dev.fyicenter.com       Good.   999     2006-04-30

-- Update "id" before "counts"
UPDATE fyi_links SET id = id+200, counts = id*2 
   WHERE url = 'dev.fyicenter.com'
GO
(1 row(s) affected)

-- Check the new values
SELECT * FROM fyi_links WHERE url = 'dev.fyicenter.com'
GO
id      url                     notes   counts  created
301     dev.fyicenter.com       Good.   202     2006-04-30

-- Reset to old values
UPDATE fyi_links SET id = 101, counts = 999 
   WHERE url = 'dev.fyicenter.com'
(1 row(s) affected)

Notice that the "id" in the "counts" new value expression is taking the old value of the "id" column, not the updated value, even the "id" column is updated before the "counts" column.

Now try this on a MySQL server, you will get different result.

⇒ Using Values from Other Tables in UPDATE Statements in SQL Server

⇐ Using Old Values to Define New Values in UPDATE Statements in SQL Server

⇑ Using INSERT, UPDATE and DELETE Statements in SQL Server

⇑⇑ SQL Server Database Tutorials