Trying to Avoid Mulitple Update Statements 2

[larrydavid]

Hello,

I am updating records that are filtered for a particular month/year like so:

     UPDATE 
		Profile_Metrics_Line_Chart_Temp 
	SET 
		Dollars_Billed_Current = cl.metricValue
	FROM 
		CHART_DATA_Temp cl
	WHERE 
		Profile_Metrics_Line_Chart_Temp.tin9 = cl.tin9 AND 
		Profile_Metrics_Line_Chart_Temp.suffix = cl.suffix AND
		cl.metric = 'Dollars_Billed_Current' AND
		cl.monthYear = 'Jan 2010'

     UPDATE 
		Profile_Metrics_Line_Chart_Temp 
	SET 
		Dollars_Billed_Current = cl.metricValue
	FROM 
		CHART_DATA_Temp cl
	WHERE 
		Profile_Metrics_Line_Chart_Temp.tin9 = cl.tin9 AND 
		Profile_Metrics_Line_Chart_Temp.suffix = cl.suffix AND
		cl.metric = 'Dollars_PAID_Current' AND
		cl.monthYear = 'Jan 2010'

However, I need to do this for Jan 2010 through Dec 2011 and realize this means I need 24 update statements for each metric and I have 12 metrics. I've been looking at looping through cursors and tried playing around with CASE WHEN THEN statements, but I'm not sure how to loop through a cursors and the CASE statements still end up being very long.

I would like to avoid having to write 288 update statements so if someone could please show me an easier way, hopefully with a set-based approach I would really appreciate it.

Thanks,
Larry

 

[SQLScholar]

Is the second one supposed to update a different column then Dollars_Billed_Current?

----------------------------------------

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Dr. Seuss

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[djj55]

If the answer to SQLscholar is yes you might try something like.

UPDATE P1 
SET 
    Dollars_Billed_Current = CASE cl.metric = 'Dollars_Billed_Current' THEN cl.metricValue ELSE P1.Dollars_Billed_Current END,  
    Dollars_PAID_Current = CASE cl.metric = 'Dollars_PAID_Current' THEN cl.metricValue ELSE P1.Dollars_PAID_Current END  
FROM Profile_Metrics_Line_Chart_Temp P1 
INNER JOIN CHART_DATA_Temp cl 
ON P1.tin9 = cl.tin9 
    AND P1.suffix = cl.suffix 
WHERE cl.monthYear = 'Jan 2010'

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[ousoonerjoe]

Would a simple IN not work? Same table, same field being updated, same fields joined, only the cl.Metric is being checked...

     UPDATE
        Profile_Metrics_Line_Chart_Temp
    SET
        Dollars_Billed_Current = cl.metricValue
    FROM
        CHART_DATA_Temp cl
    WHERE
        Profile_Metrics_Line_Chart_Temp.tin9 = cl.tin9 AND
        Profile_Metrics_Line_Chart_Temp.suffix = cl.suffix AND
        cl.metric IN ('Dollars_Billed_Current', 'Dollars_PAID_Current') AND
        cl.monthYear = 'Jan 2010';
--NOT TESTED

--------------------------------------------------
“Crash programs fail because they are based on the theory that, with nine women pregnant, you can get a baby a month.” --Wernher von Braun
--------------------------------------------------

 

[larrydavid]

Thanks for the code suggestion, much appreciated! I'm probably pushing my luck, but any chance someone could show me a way to handle the dates so I could put this all in one UPDATE statement?

Thanks,
Larry

 

[larrydavid]

Hello,
When I try to run this update statement:

UPDATE P1 
SET 
    Dollars_Billed_Current = CASE cl.metric = 'Dollars_Billed_Current' THEN cl.metricValue ELSE P1.Dollars_Billed_Current END,  
    Dollars_PAID_Current = CASE cl.metric = 'Dollars_PAID_Current' THEN cl.metricValue ELSE P1.Dollars_PAID_Current END  
FROM Profile_Metrics_Line_Chart_Temp P1 
INNER JOIN CHART_DATA_Temp cl 
ON P1.tin9 = cl.tin9 
    AND P1.suffix = cl.suffix 
WHERE cl.monthYear = 'Jan 2010'

I get the following error: Incorrect syntax near '='.

Thanks,
Larry

 

[larrydavid]

OK, the error was because it was missing the "WHEN".

Thanks,
Larry

 

[larrydavid]

Hello, this update...

UPDATE P1 
SET 
    Dollars_Billed_Current = CASE cl.metric = 'Dollars_Billed_Current' THEN cl.metricValue ELSE P1.Dollars_Billed_Current END,  
    Dollars_PAID_Current = CASE cl.metric = 'Dollars_PAID_Current' THEN cl.metricValue ELSE P1.Dollars_PAID_Current END  
FROM Profile_Metrics_Line_Chart_Temp P1 
INNER JOIN CHART_DATA_Temp cl 
ON P1.tin9 = cl.tin9 
    AND P1.suffix = cl.suffix 
WHERE cl.monthYear = 'Jan 2010'

...only returns values for the first row in the joined table. The output is null for the Dollars_PAID_Current value, but I notice that in the CHART_DATA_Temp table the first metric listed, which is Dollars_Billed_Current has its value returned. If anyone has any clue I would really appreciate it. My last grey hair is gone.

Thanks,
Larry

 

[djj55]

Hello, You need the WHEN after the CASE statement:

CASE [b]WHEN[/b] c1.metric = 'Doll...

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!