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:3532
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/sql/unsubscribe.cfm
