Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - Importance of Column Order in the SET Clause in Update Statements

By: FYIcenter.com

(Continued from previous topic...)

Is the Order of Columns in the SET Clause Important?

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.

(Continued on next topic...)

  1. What Are DML (Data Manipulation Language) Statements?
  2. How To Create a Testing Table with Test Data?
  3. How To Insert a New Row into a Table with "INSERT INTO" Statements?
  4. How To Use Column Default Values in INSERT Statements?
  5. How to provide column names in INSERT Statements?
  6. What Happens If You Insert a Duplicate Key for the Primary Key Column?
  7. How To Insert Multiple Rows with One INSERT Statement?
  8. How To Update Values in a Table with UPDATE Statements?
  9. How To Update Multiple Rows with One UPDATE Statement?
  10. How to use old values to define new values in UPDATE statements?
  11. Is the Order of Columns in the SET Clause Important?
  12. How To Use Values from Other Tables in UPDATE Statements?
  13. What Happens If the UPDATE Subquery Returns No Rows?
  14. What Happens If the UPDATE Subquery Returns Multiple Rows?
  15. How To Delete an Existing Row with DELETE Statements?
  16. How To Delete Multiple Rows with One DELETE Statement?
  17. How To Delete All Rows with DELETE Statements?
  18. How To Delete All Rows with TRUNCATE TABLE Statement?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...