SQL 2000: Variables in update statements and order of evaluation

Question

I have an update statement in this form:

declare @v as int
update tbl
set @v=tbl.a=(select sum(amount) from anothertable at where at.x = tbl.y),
    tbl.b = @v/2

The reason I would like to use a variable is to avoid using the subquery twice. The problem is that I have not found any references stating that this is safe. Is the second assignment (i.e. tbl.b = @v/2) always evaluated after the first assignment?

The order of evaluation for a select statement is not guaranteed. Is this also true for an update statement?

thanks a lot.

Answer 1

SQL is a declarative language. Don't try to do imperative constructs in it. This is not C. The order of evaluation is not guaranteed in any statement (SELECT, DELETE, UPDATE, MERGE, INSERT, really, any).

Answer 2

You could rid yourself the worry about order of evaluation with this:

UPDATE tbl
SET tbl.a = s.theSum,
    tbl.b = s.theSum / 2
FROM tbl
INNER JOIN (
    SELECT x, SUM(amount) AS thesum
    FROM anothertable
    GROUP BY x
) s ON s.x = tbl.y