Collections:
Importance of Column Order in the SET Clause in Update Statements in SQL Server
Is the Order of Columns in the SET Clause Important in SQL Server?
✍: FYIcenter.com
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:
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
2016-11-02, 1519🔥, 0💬
Popular Posts:
What Is Program Global Area (PGA) in Oracle? A Program Global Area (PGA) is a memory buffer that is ...
How to download Microsoft SQL Server 2005 Express Edition in SQL Server? Microsoft SQL Server 2005 E...
How To Convert Numeric Values to Character Strings in MySQL? You can convert numeric values to chara...
Why I Can Not Enter 0.001 Second in DATETIME values in SQL Server Transact-SQL? If you enter millise...
How To Convert a Unicode Strings to Non-Unicode Strings in SQL Server Transact-SQL? Since Unicode ch...