I want to update the column in a table based on the updation of theother column in the same table

[abinesh.agarwal@gmail.com]

Hi ,

I want to update the column in a table based on the updation of the
other column in the same table, but not getting the desired result.


DDL:

CREATE TABLE [dbo].[TestTable](
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL
) ON [PRIMARY]



Insert into (Col1, Col2, Col3) Values(1 , 1, 1)

Desired Result
Col1 = 2
Col2 = 3
Col3 = 4


This is the query that I'm using

update TestTable
set Col1= Col1 + 1,
Col2 = Col1 + 1,
Col3 = Col2 + 1


but the result I get is
Col1 = 2
Col2 = 2
Col3 = 2


Thanks for your help in advance.

[Erland Sommarskog]

Re: I want to update the column in a table based on the updation of the other column in the same table

(abinesh.agarwa l@gmail.com) writes:

I want to update the column in a table based on the updation of the
other column in the same table, but not getting the desired result.
>
>
DDL:
>
CREATE TABLE [dbo].[TestTable](
[Col1] [int] NULL,
[Col2] [int] NULL,
[Col3] [int] NULL
) ON [PRIMARY]
>
>
>
Insert into (Col1, Col2, Col3) Values(1 , 1, 1)
>
Desired Result
Col1 = 2
Col2 = 3
Col3 = 4
>
>
This is the query that I'm using
>
update TestTable
set Col1= Col1 + 1,
Col2 = Col1 + 1,
Col3 = Col2 + 1

Try

update TestTable
set Col1 = Col1 + 1,
Col2 = Col1 + 2,
Col3 = Col2 + 3

All columns in the SET clause are computed and set in parallel. It's not
that first is the first column listed computed and set, then the next one
and so on.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx