A strange question about update statement.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

A strange question about update statement.

Author

Topic

csdyyr
Starting Member

3 Posts

Posted - 2009-10-29 : 22:02:25

There are two tables with the same struture(ID datatype, COL datatype) except for data type, and I wanted to update the value of column COL to that of ID from the previovs record, the following two statements demonstrate this operation, the output from statement 1 is what I wanted but that of statement 2 is not as expected, I don't know why this would occur.

Any advice would be much appreciated!

--version: SQL Server 2000 SP4
--statement 1:
CREATE TABLE T(ID INT, COL INT) --INT
INSERT T(ID)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

CREATE CLUSTERED INDEX IDX_ID ON T(ID)

DECLARE @COL INT, @COL2 INT

UPDATE T SET @COL=@COL2,
@COL2=ID,
COL=@COL

SELECT * FROM T

DROP TABLE T
/*
ID COL
----------- -----------
1 NULL
2 1
3 2
*/

----statement 2:
CREATE TABLE T2(ID VARCHAR(8), COL VARCHAR(8)) --VARCHAR(8)
INSERT T2(ID)
SELECT '00000001' UNION ALL
SELECT '00000002' UNION ALL
SELECT '00000003'

CREATE CLUSTERED INDEX IDX_ID ON T2(ID)

DECLARE @COL VARCHAR(8), @COL2 VARCHAR(8)

UPDATE T2 SET @COL=@COL2,
@COL2=ID,
COL=@COL

SELECT * FROM T2

DROP TABLE T2
/*
ID COL
-------- --------
00000001 NULL
00000002 00000002
00000003 00000003
*/

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-10-30 : 01:25:53

[code]
UPDATE t
SET col = x.id
FROM t2 t
JOIN t2 x
On t.id = x.id + 1[/code]

csdyyr
Starting Member

3 Posts

Posted - 2009-10-31 : 04:01:52

russell,Thank you for your reply!

And here is an explanation for my question:
Because @COL of VARCHAR(8) data type is a reference type variable and @COL of INT data type is a value type variable. So when the following query was executed,
UPDATE T SET @COL=@COL2,
@COL2=ID,
COL=@COL
the execution steps like:
--step 1:Row 1
@COL=@COL2 =>@COL=NULL
@COL2=ID =>@COL2='00000001'
COL=@COL =>COL=NULL ?

--step 2:Row 2
@COL=@COL2 =>I expected it should be: @COL='00000001'
@COL2=ID =>@COL2='00000002'
COL=@COL =>I expected COL='00000001', but @COL refers to @COL2, so
COL='00000002'
The theory as descriped in step 2 best explained my question, but why it didn't apply to step 1, it's still a question to me.

csdyyr
Starting Member

3 Posts

Posted - 2009-10-31 : 22:14:18

Here is a solution:
UPDATE T2 SET @COL=CAST(@COL2+'' AS VARCHAR(8)),
@COL2=ID,
COL=@COL
But this update technique is not reliable, it's not recommended to use.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources