UPDATE - SQL Server Forums

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)

UPDATE

Author

Topic

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-24 : 05:15:59

I want to update a table based on a sort on another table.

CREATE TABLE t1 (c1 int, c2 int)
CREATE UNIQUE INDEX [t1_Idx] ON t1 (c2)

CREATE TABLE t2 (col1 int, col2 int)
CREATE UNIQUE INDEX [t2_Idx] ON t2 (col1)
GO

INSERT INTO t1
SELECT 1, 5 UNION ALL
SELECT 2, 4 UNION ALL
SELECT 3, 7 UNION ALL
SELECT 4, 1 UNION ALL
SELECT 5, 9 UNION ALL
SELECT 6, 6 UNION ALL
SELECT 7, 8

INSERT INTO t2
SELECT 1, 0 UNION ALL
SELECT 2, 0 UNION ALL
SELECT 3, 0 UNION ALL
SELECT 4, 0 UNION ALL
SELECT 5, 0 UNION ALL
SELECT 6, 0 UNION ALL
SELECT 7, 0

DECLARE @val int
SET @val = 0

now i want to update col2 of t2 based on the sort of c1 of t1 to get the followning result:

col1 col2
----------- -----------
1 5
2 4
3 7
4 1
5 9
6 6
7 8

i tried this:

SET @val = 0

UPDATE a
SET col2 = @val,
@val = @val + 1
FROM t2 a
INNER JOIN t1 b WITH (INDEX (t1_Idx))
ON a.col1 = b.c1

but it doesnt work. can anybody help me?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 05:26:55

dont you need only this?

UPDATE t2
SET t2.col2=t1.c2
FROM t2
JOIN t1
ON t1.c1=t2.col1

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-24 : 05:45:04

no it is not what i want. lets change the content of t1 as follow

INSERT INTO t1
SELECT 1, 8 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, 7 UNION ALL
SELECT 6, 17 UNION ALL
SELECT 7, 9

now i want to update col2 in t2 with a serial number starting with 1. as you can see i used a variable to update this column. i want to increment this variable and assign it to col2 according the sort of c2 in t1. that is, when i to have col2 = 1 for col1 = 4 because the c1 = 4 has the least value for c2 in t1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 05:56:57

quote:
Originally posted by daidaluus

no it is not what i want. lets change the content of t1 as follow

INSERT INTO t1
SELECT 1, 8 UNION ALL
SELECT 2, 12 UNION ALL
SELECT 3, 20 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 5, 7 UNION ALL
SELECT 6, 17 UNION ALL
SELECT 7, 9

now i want to update col2 in t2 with a serial number starting with 1. as you can see i used a variable to update this column. i want to increment this variable and assign it to col2 according the sort of c2 in t1. that is, when i to have col2 = 1 for col1 = 4 because the c1 = 4 has the least value for c2 in t1.

so what will be your t2 values for this t1 data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 06:04:54

is it this what you're looking at?

UPDATE t2
SET t2.col2=t1.seq
FROM t2
JOIN (SELECT (SELECT COUNT(*) FROM t1 WHERE c2 <=t.c2) AS Seq,*
FROM t1 t)t1
ON t1.c1=t2.col1

output
-------------------------------
col1	col2
1	3
2	2
3	5
4	1
5	7
6	4
7	6

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-24 : 06:27:36

yes. that's exactly what i need. thanks. can you tell me about the performance of this query when i use it for a large amount of data? is there any way to improve it?

daidaluus
Yak Posting Veteran

73 Posts

Posted - 2008-11-24 : 06:45:20

when i have duplicate values for for c2 (no unique constraint) i get duplicate values for seq. is there any workaround? i need a unique sequence of numbers.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 06:47:31

quote:
Originally posted by daidaluus

yes. that's exactly what i need. thanks. can you tell me about the performance of this query when i use it for a large amount of data? is there any way to improve it?

yup. the subquery will perform badly in case of large data. b/w are you using sql 2005?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 06:48:40

quote:
Originally posted by daidaluus

when i have duplicate values for for c2 (no unique constraint) i get duplicate values for seq. is there any workaround? i need a unique sequence of numbers.

If you were using sql 2005 this would have been much easire using window functions. in case of duplicate values you might need a temporary table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources