June 27, 2005 at 1:19 am
How can I determine which column in a particular row in a table contains a particular value? I then need to update another table using the result of this query.
Example:
t1
Col1 Col2 Col3 Col4 .. Coln
1 abc abc abc xyz .. abc
2 xyz abc abc abc .. abc
3 abc abc xyz abc .. abc
t2
Col1 Col2 Col3 Col4 .. Coln
1 0 0 0 0 .. 0
2 0 0 0 0 .. 0
3 0 0 0 0 .. 0
For row 1 set t2.Col4 = 123, for row 2 set t2.Col1 = 123 etc.
I need to do this for multiple table pairs with variable no of columns, t1 & t2 40 columns, t3 & t4 234 columns, t5 & t6 5 columns ...
Ian Scott
June 27, 2005 at 6:55 am
What is the relationship between t1 and t2? What is the business rule for determining the value in t2?
Mike
June 27, 2005 at 4:41 pm
I didn't explain that very well!!
In row 1 of t1, col4 contains 'xyz' so update t2 set Col4 = avalue
In row 2 of t1, Col1 contains 'xyz' so update t2 set Col1 = avalue
In row 3 of t1, Col3 contains 'xyz' so update t2 set Col3 = avalue
avalue is derived elsewhere and has no relationship with either table and may vary. 'xyz' can be any value determined elsewhere and may vary.
That is what I want to happen.
June 28, 2005 at 5:52 am
You would have to Cut-n-paste a lot, but maybe this helps:
create table #t1 (pk_id int,col1 varchar(5), col2 varchar(5), col3 varchar(5))
create table #t2 (pk_id int,col1 int, col2 int, col3 int)
insert #t1 select 1,'abc','abc','xyz'
insert #t1 select 2,'abc','xyz','abc'
insert #t1 select 3,'abc','xyz','abc'
insert #t2 select 1,0,0,0
insert #t2 select 2,0,0,0
insert #t2 select 3,0,0,0
declare @v1 varchar(10),
@v2 int
set @v1 = 'xyz'
set @v2 = 123
update a
set a.Col1 = case when b.Col1 = @v1 then @v2 else a.Col1 end,
a.Col2 = case when b.Col2 = @v1 then @v2 else a.Col2 end,
a.Col3 = case when b.Col3 = @v1 then @v2 else a.Col3 end
from #t2 a,
#t1 b
where a.PK_ID = b.PK_ID --Assuming you have this
select * from #t1
select * from #t2
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply