Actually it can be accomplished with a case statement. I'm assuming this is
T-SQL:
I also accounted for a missing Winter tier for Grade 8
UPDATE AIMSweb_Winter2014_Skyimport
SET WINTERTIER =
CASE
WHEN GRADE = '1' AND WINTERSCORE > 2 THEN '1'
WHEN GRADE = '1' AND WINTERSCORE = 2 THEN '2'
WHEN GRADE = '1' AND WINTERSCORE < 2 THEN '3'
WHEN GRADE = '2' AND WINTERSCORE > 8 THEN '1'
WHEN GRADE = '2' AND WINTERSCORE BETWEEN 5 AND 8 THEN '2'
WHEN GRADE = '2' AND WINTERSCORE < 5 THEN '3'
WHEN GRADE = '3' AND WINTERSCORE > 13 THEN '1'
WHEN GRADE = '3' AND WINTERSCORE BETWEEN 9 AND 13 THEN '2'
WHEN GRADE = '3' AND WINTERSCORE < 9 THEN '3'
WHEN GRADE = '4' AND WINTERSCORE > 18 THEN '1'
WHEN GRADE = '4' AND WINTERSCORE BETWEEN 13 AND 18 THEN '2'
WHEN GRADE = '4' AND WINTERSCORE < 13 THEN '3'
WHEN GRADE = '5' AND WINTERSCORE > 20 THEN '1'
WHEN GRADE = '5' AND WINTERSCORE BETWEEN 14 AND 20 THEN '2'
WHEN GRADE = '5' AND WINTERSCORE < 14 THEN '3'
WHEN GRADE = '6' AND WINTERSCORE > 16 THEN '1'
WHEN GRADE = '6' AND WINTERSCORE BETWEEN 20 AND 26 THEN '2'
WHEN GRADE = '6' AND WINTERSCORE < 20 THEN '3'
WHEN GRADE = '7' AND WINTERSCORE > 24 THEN '1'
WHEN GRADE = '7' AND WINTERSCORE BETWEEN 18 AND 24 THEN '2'
WHEN GRADE = '7' AND WINTERSCORE < 19 THEN '3'
-- ORIGINAL CODE MISSING A WINTERSCORE > 20 AND GRADE = 8
WHEN GRADE = '8' AND WINTERSCORE > 20 THEN '1'
WHEN GRADE = '8' AND WINTERSCORE BETWEEN 15 AND 20 THEN '2'
WHEN GRADE = '8' AND WINTERSCORE < 15 THEN '3'
ELSE WINTERTIER
END
WHERE GOM = 'MAZE';
Steven Durette
Professional Applications Developer
AT&T Network Operations - Construction and Engineering
sd1...@att.com
(810) 984-6684 [work]
(810) 334-7778 [personal cell]
2020 Bancroft St
Room 22
Port Huron, MI 48060
-----Original Message-----
From: Carl Von Stetten [mailto:vonner.li...@vonner.net]
Sent: Tuesday, February 04, 2014 1:43 PM
To: sql
Subject: Re: sql query help
You can't combine all of them into one query, since you are setting
WinterTier to different values in different queries. You can combine
some if they are setting the same value by using a combination of AND/OR
statements in the WHERE clause. Here's a hint on combining the first two:
UPDATE AIMSweb_Winter2014_Skyimport si
SET si.WinterTier = '3'
WHERE si.GOM = 'MAZE'
AND (
( si.Grade = '1' AND si.WinterScore <2 )
OR
( si.Grade = '2' AND si.WinterScore <5 )
)
You could continue to add more OR statements to handle all of the rest of the
queries where you are setting WinterTier to 3. Then use a separate similar
query where you are setting WinterTier to a different value.
FYI - note that I created an alias "si" for your very long table name and used
that alias throughout for brevity. You could even omit the alias and the query
name prefixes on all the columns since you are only dealing with one table.
-Carl V.
On 2/4/2014 10:28 AM, Zimmerman, Debi wrote:
> What do I need to do so that I can combine the following update queries
> to run together instead of running each one separately. Any help is
> appreciated.
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '1'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <2
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '2'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <5
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '3'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <9
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '4'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <13
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '5'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <14
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '6'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <20
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '7'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <18
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '3'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '8'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore <15
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '1'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >2
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '2'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >8
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '3'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >13
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '4'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >18
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '5'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >20
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '6'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >26
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '7'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >24
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '1'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '8'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore >20
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '1'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore =2
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '2'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 5 AND 8
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '3'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 9 AND 13
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '4'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 13 AND 18
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '5'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 14 AND 20
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '6'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 20 AND 26
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '7'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 18 AND 24
>
>
>
> UPDATE AIMSweb_Winter2014_Skyimport
>
> SET AIMSweb_Winter2014_Skyimport.WinterTier = '2'
>
> WHERE AIMSweb_Winter2014_Skyimport.Grade = '8'
>
> AND AIMSweb_Winter2014_Skyimport.GOM = 'MAZE'
>
> AND WinterScore BETWEEN 15 AND 20
>
>
>
>
>
> Debi Zimmerman
>
> Software Support Specialist
>
> Garden City Public Schools
>
> 620.805.7109
>
>
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion
Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3533
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
