Updating multiple columns in a table
Hello SQL Server Readers, You've probably seen multiple column update examples out there using syntax such as this: set (col1, col2) = (select col1, col2 from ...) You've also probably found that the above syntax is not SQL Server friendly.Apparently this is Oracle syntax and very well may work on other databases too, so long as they support 'row value constructors'.From what I've read, this is what you'd call using the update statement extension.
Now, without further ado, the example: Just happened to stumble across this blog while researching another issue and thought I'd throw my slight variation out there:(assuming you have a unique key to join on)UPDATE table1 SETcolumn1 = a.column1,column2 = a.column2,column3 = a.column3FROM table1 JOIN table2 a ON table1. ID I've tried to use your example to update,but I am using fully qualified table name like [database].[schema].[table].[column].
In the outer where clause (if you choose to use one) be sure to qualify your column name.
This may not be necessary if you're subselect does not have the same column names as your update statement.
You need to specify all keys in a table having compound and clustering columns.
For example, update the value of a column in a table having a compound primary key, userid and url: UPDATE excelsior.clicks USING TTL 432000 SET user_name = 'bob' WHERE userid=cfd66ccc-d857-4e90-b1e5-df98a3d40cd6 AND url=' UPDATE Movies SET col1 = val1, col2 = val2 WHERE movie ID = key1; UPDATE Movies SET col3 = val3 WHERE movie ID IN (key1, key2, key3); UPDATE Movies SET col4 = 22 WHERE movie ID = key4; You can increase or decrease the value of a counter column by an arbitrary numeric value though the assignment of an expression that adds or substracts the value.
TTL marks the inserted values, not the column itself, for expiration.