update query - 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 query

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-02-11 : 11:48:40

Ok...we have a table that has data like the following..

id col1
12 A
12 B
13 ADD
13 ARS
13 TLF
14 NKJ
15 LKJ

this table could have more than one row per client (T1), I need to update a table that has only one row per client (T2). This second table(T2) has several columns such as col1value, col2value, etc. For example, for the client with the id 12, I need to update his row in the other table (T2) putting the first col1 value (A) in col1value and since he has another row, I need to put the next value (B) in col2value. The client with the id of 13 would have his one row in T2 update three times filling col1value, col2value, col3value...etc.

How would I write this update?

Hope this makes sense,

Eddie

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-02-11 : 12:47:35

This is pretty ugly, but it works... You have to update each column in the second table with a seperate update statement. I'm sure there is a more efficient way to do this, but this will get you through for a one time deal.

update T2
set T2.col1value = (select top 1 T1.col1
from T1
where T2.id = T1.id
order by T1.col1)

update T2
set T2.col2value = (select top 1 T1.col1
from T1
where T2.id = T1.id
and (T1.col1 not in (select top 1 temp_t1.col1
from t1 as temp_t1
where T2.id = temp_t1.id
order by temp_t1.col1))
order by T1.col1)

update T2
set T2.col3value = (select top 1 T1.col1
from T1
where T2.id = T1.id
and (T1.col1 not in (select top 2 temp_t1.col1
from t1 as temp_t1
where T2.id = temp_t1.id
order by temp_t1.col1))
order by T1.col1)

update T2
set T2.col4value = (select top 1 T1.col1
from T1
where T2.id = T1.id
and (T1.col1 not in (select top 3 temp_t1.col1
from t1 as temp_t1
where T2.id = temp_t1.id
order by temp_t1.col1))
order by T1.col1)

Jeff Banschbach
Consultant, MCDBA

eddie
Starting Member

45 Posts

Posted - 2002-02-11 : 14:54:07

Thanks Jeff,

We are running it now and it appears to be working! Thanks for the help!

Eddie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources