August 27, 2006 at 6:34 pm
Hello,
I have two Tables T1 and T2. I want to update some of the values of T2 based on the values in T1.
Create Table T1 (f1 int, f2 int, f3 char(1), f4 char(1))
insert into T1 values (1, 1, 'a', 'b')
insert into T1 values (2, 1, 'x', 'z')
insert into T1 values (3, 2, 'd', 'c')
insert into T1 values (4, 2, 'x', 'z')
insert into T1 values (5, 2, 'p', 'q')
insert into T1 values (6, 3, 'a', 'b')
insert into T1 values (7, 4, 'a', 'b')
field f1 is auto incremental (identity Column)
create table T2 (f2 int, f3 char(1), f4 char(1))
insert into T1 values (1, '', '')
insert into T1 values (2, '', '')
insert into T1 values (3, '', '')
insert into T1 values (4, '', '')
I want to update T2 for matching values of f2 from T1 where f1 field is having highest value.
so output of select * from T2 should be
f2 f3 f4
1 x z
2 p q
3 a b
4 a b
I have around 150,000 rows in T1 with only 1000 records which are having duplicates of f2. All rest of the records are having only one unique row
for f2 field. i can somehow write some query and get the required result, but it is not cost effective and is taking too much of time. Can someone
tell me what would be the most efficient way to update T2 from T1 without causing burder on query.
Thanks,
Ramesh.
August 28, 2006 at 4:13 am
UPDATE T2
SET T2.f3 = T1.f3,
T2.f4 = T1.f4
FROM T2
INNER JOIN (
SELECT f2,
MAX(f1) f1
FROM T1
GROUP BY f2
) q ON q.f2 = T2.f2
INNER JOIN T1 ON T1.f1 = q.f1
N 56°04'39.16"
E 12°55'05.25"
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply