Prev: Cannot see SQL Server 2008 on other node!
Next: Remove characters in an ASCII decimal range: How To?
From: tshad on 14 May 2010 20:10
Just want to make sure here.

If you are updating multiple fields, it updates in the order that you
specify, correct?

For example:

columnA= 100
columnB = 200
columnC = NULL

I am going to move columnA to columnC, columnB to columnA, set columnB to
NULL.

This should end up with

columnA = 200
columnB = NULL
columnC = 100

This only works if you do it in the correct order. On my machine it works
correctly, but I want to make sure that isn't a coincedence.

The script I would use would be:

UPDATE tableA
SET columnC = columnA,
columnA = columnB,
columnC = null

Is it guaranteed to be done in this order? Any other order would not come
up correctly.

I assume this is correct but want to make sure.

Thanks,

Tom


From: Plamen Ratchev on 14 May 2010 22:25
SQL applies the update "at once". This is why you can swap column
values with:

UPDATE Foo
SET column_a = column_b, column_b = column_a;


So there is no order of applying the updates to the columns, but the
values are updated at once which makes it work in your case.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 17 May 2010 14:06
No sure what you mean by "at once". I understand that when refering to rows
but here the change is made to the

If column_a = 10 and column_b = 20, the order you do this does make a
difference unless the values are put into temporary variables.

if column_a=column_b is done first, you end up with:

column_a = 20 and column_b = 20,

if column_b = column_a is done first, then you end up with:

column_a = 10 and column_b = 10

If a tempory variable is used to store the location before the move then
you swap values. Is this what you mean by "at once"?

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:i91su5dvhoh9pfj873t2uqmcch3r0nheud(a)4ax.com...
> SQL applies the update "at once". This is why you can swap column
> values with:
>
> UPDATE Foo
> SET column_a = column_b, column_b = column_a;
>
>
> So there is no order of applying the updates to the columns, but the
> values are updated at once which makes it work in your case.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Sylvain Lafontaine on 17 May 2010 14:48
"tshad" <tfs(a)dslextreme.com> wrote in message
news:uJGxYue9KHA.3276(a)TK2MSFTNGP02.phx.gbl...
> No sure what you mean by "at once". I understand that when refering to
> rows but here the change is made to the
>
> If column_a = 10 and column_b = 20, the order you do this does make a
> difference unless the values are put into temporary variables.
>
> if column_a=column_b is done first, you end up with:
>
> column_a = 20 and column_b = 20,
>
> if column_b = column_a is done first, then you end up with:
>
> column_a = 10 and column_b = 10
>
> If a tempory variable is used to store the location before the move then
> you swap values. Is this what you mean by "at once"?

Yes it is and you can easily test for that. All the old values are read int
temporary variables and then the new values are written back to the record.
There is no multiple readings or mix of read & write operations on the same
record.

>
> Thanks,
>
> Tom
>
> "Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
> news:i91su5dvhoh9pfj873t2uqmcch3r0nheud(a)4ax.com...
>> SQL applies the update "at once". This is why you can swap column
>> values with:
>>
>> UPDATE Foo
>> SET column_a = column_b, column_b = column_a;
>>
>>
>> So there is no order of applying the updates to the columns, but the
>> values are updated at once which makes it work in your case.
>>
>> --
>> Plamen Ratchev
>> http://www.SQLStudio.com

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)


From: Plamen Ratchev on 17 May 2010 21:29
Think of having a snapshot of the column values before the update,
then use the snapshot values to update the columns. All column values
are update in a single step (using the snapshot values), there is no
order of applying the update. This is a fundamental concept in SQL,
which makes it differ from the procedural languages (where iteration
and order rule).

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2 3
Prev: Cannot see SQL Server 2008 on other node!
Next: Remove characters in an ASCII decimal range: How To?