# Sql-server – way to copy/initialize values in a set

azure-sql-databasesql serversql-server-2008update

What I want to do (I know this does not work) is:

update stats
set BandwidthLastMonth = BandwidthThisMonth,
BandwidthThisMonth=0
where myPk = 3


Is there a way to do this in a select statement? Or do I need to write a stored procedure and hit each record twice (I sure hope not).

My solution needs to work for Sql Azure and Sql Server 2008.

Until we see the actual code, we can only speculate but for what is worth, I'll do a guess.

Assuming that your code involves only a table and its columns (no @variables involved) and is something like:

UPDATE
tablename
SET
column_a = column_b,
column_b = 0
WHERE
(condition) ;


It is valid SQL and 100% correct. Correct, meaning that after the update column_a will have the values that column_b had before the update and column_b will have 0, for all rows that pass the (condition).

SQL-Server follows this behaviour strictly and there is no reason to assume that it will deviate from the standard in a future release.

SQL works in sets and the updates are done "simultaneously" on the whole row. (they may not be done simultaneously but the result is the same as if they were.) You could change the code to this and the result will be (guaranteed) the same:

...
SET
column_b = 0,
column_a = column_b
...


In fact, the following is valid and equivalent SQL as well (although not yet implemented in SQL-Server);

UPDATE
tablename
SET
(column_a, column_b) = (column_b, 0)
WHERE
(condition) ;


I haven't tested Azure but if it deviated from this basic SQL behaviour, it would better be documented somewhere.