Update order

[Yannick Turgeon]

Hello,

I'm using SS2000 on NT4.

If I run the following query, is it garanteed that the field
"OldField1Value" will hold the value Field1 had before the query was run?

UPDATE MyTable
SET Field1 = 42,
OldField1Value = Field1
.... and in this query?

UPDATE MyTable
SET OldField1Value = Field1,
Field1 = 42

[Rich Dillon]

Yannick,

Yes. OldField1Value will be set to the value of Field1 prior to the query's
execution in both cases. Think of all the columns in being updated in the
same instant rather than one after the other. The order in which you
specify the columns after SET is not significant.

Try this for a demonstration:

CREATE TABLE foo (
code CHAR(1) NOT NULL PRIMARY KEY,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL);

INSERT INTO foo VALUES ('A',1,3,7);
INSERT INTO foo VALUES ('B',2,5,8);

UPDATE foo
SET c1=c2+1, c2=c3+1, c3=c1+1;
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:xW*******************@news20.bellglobal.com.. .

Hello,

I'm using SS2000 on NT4.

If I run the following query, is it garanteed that the field
"OldField1Value" will hold the value Field1 had before the query was run?

UPDATE MyTable
SET Field1 = 42,
OldField1Value = Field1
... and in this query?

UPDATE MyTable
SET OldField1Value = Field1,
Field1 = 42

[Yannick Turgeon]

I just realised that your test exclude any possible "lucky" sequence. This
is a definite confirmation!

"Rich Dillon" <ri********@mindspring.com> wrote in message
news:bg**********@slb3.atl.mindspring.net...

Yannick,

Yes. OldField1Value will be set to the value of Field1 prior to the query's execution in both cases. Think of all the columns in being updated in the
same instant rather than one after the other. The order in which you
specify the columns after SET is not significant.

Try this for a demonstration:

CREATE TABLE foo (
code CHAR(1) NOT NULL PRIMARY KEY,
c1 INT NOT NULL,
c2 INT NOT NULL,
c3 INT NOT NULL);

INSERT INTO foo VALUES ('A',1,3,7);
INSERT INTO foo VALUES ('B',2,5,8);

UPDATE foo
SET c1=c2+1, c2=c3+1, c3=c1+1;
"Yannick Turgeon" <no****@nowhere.com> wrote in message
news:xW*******************@news20.bellglobal.com.. .

Hello,

I'm using SS2000 on NT4.

If I run the following query, is it garanteed that the field
"OldField1Value" will hold the value Field1 had before the query was run?
UPDATE MyTable
SET Field1 = 42,
OldField1Value = Field1
... and in this query?

UPDATE MyTable
SET OldField1Value = Field1,
Field1 = 42