Based on previous question update query needed,

Question

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/f9dcbc27-a933-4f8b-84c0-f67df74e381e/

Please read this before. Tom did an excellent job.

I need to create an update for the following:

SELECT     
 OpRate.SnapShotID,  
 OpRate.Type,
 Max(Case When OpRate.Usage = 'H' Then OpRate.MaxTerm End) As MaxTerm_Heavy_Rate,  
 Max(Case When OpRate.Usage = 'L' Then OpRate.MaxTerm End) As MaxTerm_Light_Rate,  
 Max(Case When OpRate.Usage = 'M' Then OpRate.MaxTerm End) As MaxTerm_Medium_Rate,
 Max(Case When OpRate.Usage = 'H' Then OpRate.UsageRate End) As UsageRate_Heavy_Rate,  
 Max(Case When OpRate.Usage = 'L' Then OpRate.UsageRate End) As UsageRate_Light_Rate,  
 Max(Case When OpRate.Usage = 'M' Then OpRate.UsageRate End) As UsageRate_Medium_Rate,   
 CASE WHEN Engine = 'D' THEN 'Diesel'  
   WHEN Engine = 'E' THEN 'Electric'  
   WHEN Engine = 'P' THEN 'Propane'  
   WHEN Engine = 'G' THEN 'Gas'  
   WHEN Engine = 'H' THEN 'Hybird'  
   ELSE 'NONE' END AS EngineName, 
 Types.TypeDescription 
FROM OpRate 
INNER JOIN Types ON OpRate.Type = Types.Type  
GROUP BY OpRate.SnapShotID, Types.TypeDescription, OpRate.Type, Engine 

What happen above is a select that was giving one row instead of three.

I have a grid that needs to bind an update query with the above parameters.

SnapShotID Type MaxTerm_Heavy_Rate MaxTerm_Light_Rate MaxTerm_Medium_Rate UsageRate_Heavy_Rate UsageRate_Light_Rate UsageRate_Medium_Rate EngineName TypeDescription
1 101 7 7 7 120.80 84.56 102.68 Diesel SEDAN COMPACT
1 101 7 7 7 100.80 70.56 85.68 Gas SEDAN COMPACT
1 101 7 7 7 120.80 84.56 102.68 Hybird SEDAN COMPACT

Now for instance if a user has selected a Gas Engine (Row 2)

The user should be able to do one update instead of three.

My original query

returned

SnapShotID Type TypeDescription EngineName UsageRate MaxTerm Engine Usage
1 101 SEDAN COMPACT Diesel 120.80 7 D H
1 101 SEDAN COMPACT Diesel 84.56 7 D L
1 101 SEDAN COMPACT Diesel 102.68 7 D M
1 101 SEDAN COMPACT Gas 100.80 7 G H
1 101 SEDAN COMPACT Gas 70.56 7 G L
1 101 SEDAN COMPACT Gas 85.68 7 G M
1 101 SEDAN COMPACT Hybird 120.80 7 H H
1 101 SEDAN COMPACT Hybird 84.56 7 H L
1 101 SEDAN COMPACT Hybird 102.68 7 H M

This means if I have to do an update on the engine I would have to create three updates, to modify for instance Engine-Gas

1 101 SEDAN COMPACT Gas 100.80 7 G H
1 101 SEDAN COMPACT Gas 70.56 7 G L
1 101 SEDAN COMPACT Gas 85.68 7 G M

where now I have

1 101 SEDAN COMPACT Gas 100.80 70.56 85.68 7 7 7

This means inorder for the user to do an update on three rows for a type = '101' and Engine='G' (Gas) on

Usage=H (Heavy)

Usage=L (Light)

Usage=M (Medium)

Type Number: 101  
Type Description: SEDAN COMPACT
Engine:   Gas 

Heavy Rate: 100.80  Max Term for Heavy:  7
Meduim Rate: 85.68  Max Term for Medium: 7 
Light Rate: 70.56   Max Term for Light:  7
UPDATE          

SQLStr = "UPDATE dbo.OpRate SET UsageRate = @Rate, MaxTerm = @Term WHERE SnapShotID = 1 AND Type = @Type AND Engine = @Engine AND Usage = 'H';"
                cmd = New SqlCommand(SQLStr, CON)
                cmd.Parameters.Add("@Type", SqlDbType.Int).Value = Trim(TypeNoTextBox.Text)
                cmd.Parameters.Add("@Engine", SqlDbType.VarChar).Value = PanelEngineDropDownList.SelectedValue
                cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = CType(Trim(TextBox1.Text), Decimal)
                cmd.Parameters.Add("@Term", SqlDbType.Int).Value = CInt(Trim(HeavyMaxTermTextBox.Text))
                CON.Open()
                cmd.ExecuteNonQuery()
                CON.Close()


SQLStr = "UPDATE dbo.OpRate SET UsageRate = @Rate, MaxTerm = @Term WHERE SnapShotID = 1 AND Type = @Type AND Engine = @Engine AND Usage = 'M';"
                cmd = New SqlCommand(SQLStr, CON)
                cmd.Parameters.Add("@Type", SqlDbType.Int).Value = Trim(TypeNoTextBox.Text)
                cmd.Parameters.Add("@Engine", SqlDbType.VarChar).Value = PanelEngineDropDownList.SelectedValue
                cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = CType(Trim(TextBox2.Text), Decimal)
                cmd.Parameters.Add("@Term", SqlDbType.Int).Value = CInt(Trim(MediumMaxTermTextBox.Text))
                CON.Open()
                cmd.ExecuteNonQuery()
                CON.Close()



SQLStr = "UPDATE dbo.OpRate SET UsageRate = @Rate, MaxTerm = @Term WHERE SnapShotID = 1 AND Type = @Type AND Engine = @Engine AND Usage = 'L';"
                cmd = New SqlCommand(SQLStr, CON)
                cmd.Parameters.Add("@Type", SqlDbType.Int).Value = Trim(TypeNoTextBox.Text)
                cmd.Parameters.Add("@Engine", SqlDbType.VarChar).Value = PanelEngineDropDownList.SelectedValue
                cmd.Parameters.Add("@Rate", SqlDbType.Decimal).Value = CType(Trim(TextBox3.Text), Decimal)
                cmd.Parameters.Add("@Term", SqlDbType.Int).Value = CInt(Trim(LightMaxtermTextBox.Text))
                CON.Open()
                cmd.ExecuteNonQuery()
                CON.Close()

The three queries would update the form above.

I need to create one update. any help would be great.

Thank you.

---

Mathieu Alexandre Cupryk www.omegalove.com

Answer 1

Hope this helps.

UPDATE
	dbo.OpRate
SET
	UsageRate 
	=  
	CASE Usage
		WHEN 'H' THEN @UsageRate_Heavy_Rate
		WHEN 'M' THEN @UsageRate_Medium_Rate
		WHEN 'L' THEN @UsageRate_Light_Rate
		ELSE UsageRate
	END,
	MaxTerm
	=
	CASE Usage
		WHEN 'H' THEN @MaxTerm_Heavy_Rate
		WHEN 'M' THEN @MaxTerm_Medium_Rate 
		WHEN 'L' THEN @MaxTerm_Light_Rate 
		ELSE MaxTerm
	END
WHERE
	SnapShotID = 1
AND
	Type = @Type
AND
	Engine = @Engine
AND
	Usage IN ('H',' M','L');

This is under the assumption that the Parameters (@UsageRate_Heavy_Rate, @UsageRate_Medium_Rate, @UsageRate_Light_Rate, @MaxTerm_Heavy_Rate, @MaxTerm_Medium_Rate, @MaxTerm_Light_Rate) corresponds to the column of the same name.

Please mark this post as answer, if it answers your question.

---

Service Delivery & Support Management

Answer 2

When I try this in sql

it updates two rows

SnapShotID	Type	Engine	Usage	UsageRate	MaxTerm
1	101	G	H	1	5
1	101	G	L	3	5
1	101	G	M	85.68	7


When I try this in sql it only updates the two rows
USE [Minotaur]
GO


UPDATE
	dbo.OpRate
SET
	UsageRate 
	=  
	CASE Usage
		WHEN 'H' THEN '1'
		WHEN 'M' THEN '2'
		WHEN 'L' THEN '3'
		ELSE UsageRate
	END,
	MaxTerm
	=
	CASE Usage
		WHEN 'H' THEN '5'
		WHEN 'M' THEN '5'
		WHEN 'L' THEN '5'
		ELSE MaxTerm
	END
WHERE
	SnapShotID = '1'
AND
	Type = '101'
AND
	Engine = 'G'
AND
	Usage IN ('H',' M','L');

I am not sure what to modify

---

Mathieu Alexandre Cupryk www.omegalove.com

Answer 3

My Bad. It is because in the WHERE Clause, 'M' has a preceding space. Hence the records specific to Usage = 'M' are not selected for UPDATE. Kindly correct it and retry.

Usage IN ('H','M','L');

Please mark the original post as answer, if it answers your question.

---

Service Delivery & Support Management

Answer 4

All good I saw it right away. Thanks.

---

Mathieu Alexandre Cupryk www.omegalove.com