How to case update 1

[Naith]

Hi,

I want to update one of 13 columns in one table. Which column is to be updated depends on the value in another table.

In essence, if the value in the source table is 'C', then I want to take the third measures column from this table and update the third measures column in the target table. If it's 'D', then the fourth, and so on. (It has to be update rather than insert, because the number of rows may be inconsistent).

What's the best way to approach this?

Thanks

 

[AlexCuse]

If you have a set number of values in the source table (and a set number of columns to potentially update) you could write a stored procedure to handle everything. Someone else may have a more glamorous solution though.

HTH,

Alex

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[AlexCuse]

Hi Naith,

I think I've come up with something for you that will not involve quite so many lines of code. I'd still save it as an SP though, and possibly add error handling.

declare @varcol varchar(10)
declare @varfield varchar(20)
declare @ex varchar (75)
declare @RCNT int
declare @temp table (varfield varchar (20))

select @RCNT = count(*) from xtest

WHILE (@RCNT <> 0) BEGIN

select @varcol =  
(CASE WHEN max([FieldName]) like 'XXX%' then 'Col001'
WHEN max([FieldName]) like 'XXY%' then 'Col002'
ELSE 'Col003' END) from xtest where [FieldName] not in (select varfield from @temp)

select @varfield = max([FieldName]) from xtest where [FieldName] not in (select varfield from @temp)


select @ex = 'UPDATE xtest SET ' + @varcol + ' = FileRC where [FieldName] LIKE ''' + @varfield + '%'''

EXEC (@ex)

set @RCNT = @RCNT - 1

INSERT INTO @TEMP ([Varfield])
select @varfield

END

The field and table names are all junk that I threw into a table, but you should be able to make this work with some minor tweaks. Everything worked well in my 'xtest' table.

Changing this part could help you:

select @ex = 'UPDATE xtest [COLOR=red]a inner join ytest b [/color]
 SET a.' + @varcol + [COLOR=red]' = b.' + @varcol + [/color red]' where a.[FieldName] LIKE ''' + @varfield + '%'''

(These changes are not tested)

If you have an ID column you could use in place of [FieldName] it would make things easier I think.
Hope it helps,

Alex


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Naith]

Thanks man. I'll jiggle this around a bit and then let you know.

Cheers,

Naith

 

[AlexCuse]

Let me know how it works for you, and if you needed to make any additional changes. That seems like one that could come in handy in the future.

It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Naith]

Hey buddy,

Thanks again for taking the time out to look into a solution for this. I used your approach and tweaked it to suit my situation.

However, when I run the procedure, no update takes place at all. Can you see anything wrong with this:

create procedure Forecast_History 
@loadweek tinyint
as
declare @varcol varchar(10)
declare @ex varchar (75)

select @varcol =  
(CASE 
WHEN @loadweek = 1 then week13
WHEN @loadweek = 2 then week01
WHEN @loadweek = 3 then week02
WHEN @loadweek = 4 then week03
WHEN @loadweek = 5 then week04
WHEN @loadweek = 6 then week05
WHEN @loadweek = 7 then week06
WHEN @loadweek = 8 then week07
WHEN @loadweek = 9 then week08
WHEN @loadweek = 10 then week09
WHEN @loadweek = 11 then week10
WHEN @loadweek = 12 then week11
WHEN @loadweek = 13 then week12
END) 
from Forecast

select @ex = 'UPDATE FC_History a inner join Forecast b 
SET a.' + @varcol + ' = b.' + @varcol + ' where a.customernumber = b.customernumber and a.product = b.product'

EXEC (@ex)

Cheers,
Naith

 

[AlexCuse]

If I am not mistaken, the 75 char limit on @ex will only take you to here:

'UPDATE FC_History a inner join Forecast b 
SET a.' + @varcol + ' = b.' + @varcol + ' where [COLOR=red] a.cu
[/color]

The only reason I'm so quick to point it out is because I started out using a 50 char limit and had to up it to 75 to get it to print the whole statement. Let me know if this works for you.

Alex



It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Naith]

You're right - I didn't spot that. I would have thought that should throw out an error, but it didn't - probably because it isn't executing the statement.

I upped the limit of ex to 120, but still have the same problem; no updates.

 

[Naith]

I found the problem; I was missing any caffeine.

Once I added that, (and put quotes around the varcol values), it was right as rain.

Thanks again, Alex. You a star.

 

[AlexCuse]

Ah I didn't see that with the varcol values either. I must've been suffering from caffeine deficiency as well...

I'm really glad it worked, I think this function will be very useful in the future. I like how you passed in @loadweek, is that user defined or programmatically determined?


It's a magical time of year in Philadelphia. Eagles training camp marks the end of another brutal season of complaining about the Phillies.

 

[Naith]

Cheers Alex. It's passed in at runtime from the frontend.