Link to home
Start Free TrialLog in
Avatar of doug-t
doug-tFlag for United States of America

asked on

sql2008 update query

I have a database that contains the following fields,  
academic1
academic2
choice1
choice2
choice3
choice4
choice5

I link that to another table that I can join on id's.  if academic1 = 0 then I want to update academic2 with a value of 1.  if academic1 = 1 then I want to update one of the choice fields based on whether or not they have a value of 0.  For example if choice1 = 0 I want to set it to 1 but I do not want to set choice2 - choice5 to 1 unless of course choice1 was already set to 1.

I put the logic below but of course it doesn't kick out of the update after it updates choice1, it puts a 1 in all the values of all the choice fields.  I've tried an If statement but can't seem to get the syntax for that correct.  I need to be able to exit the update once one of the conditions is true.

update COO_CONVO_ATTENDANCE
set
ACADEMIC2 =
            case when ACADEMIC1 = 0 then 1
                 when ACADEMIC1 = 1 and ACADEMIC2 = 0 and ACADEMIC_DEFICIENT > 0 then 1
            else
            ACADEMIC2
            end,
CHOICE1 =
            case when CHOICE1 = 0 then 1
            else
            CHOICE1
            end,
CHOICE2 =
            case when CHOICE2 = 0 then 1
            else
            CHOICE2
            end,
CHOICE3 =
            case when CHOICE3 = 0 then 1
            else
            CHOICE3
            end,
CHOICE4 =
            case when CHOICE4 = 0 then 1
            else
            CHOICE4
            end,
CHOICE5 =
            case when CHOICE5 = 0 then 1
            else
            CHOICE5
            end
from COO_CONVO_ATTENDANCE,
     coo_001_import_ids
where coo_convo_attendance.ID =  coo_001_import_ids.import_id
Avatar of jogos
jogos
Flag of Belgium image

<<I put the logic below but of course it doesn't kick out of the update after it updates choice1, it puts a 1 in all the values of all the choice fields.  I've tried an If statement but can't seem to get the syntax for that correct.  I need to be able to exit the update once one of the conditions is true.>>
Not possible in one statement without including testing in each next update if the previous statement are already met.
Avatar of doug-t

ASKER

Thanks jogos, that was my next direction if there wasn't another way.
ASKER CERTIFIED SOLUTION
Avatar of Chris__W
Chris__W
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of doug-t

ASKER

Thanks Chris
Hello Doug - did it work out or is there still an issue?

Chris
Avatar of doug-t

ASKER

yes it worked.  Thank you
Excellent, please mark the question as answered.

Chris