sql2008 update query

doug-t
doug-t used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
<<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.

Author

Commented:
Thanks jogos, that was my next direction if there wasn't another way.
Commented:
Hello,

Below is the code for what you are describing.  However, you could alternatively build the update script at runtime using a stored procedure to concatenate only the peices you are looking for.  This would allow you greater flexibility, but has all the usual pros and cons of stored procedures and dynamic SQL.  

update COO_CONVO_ATTENDANCE
set 
ACADEMIC2 =
            case when ACADEMIC1 = 0 then 1
            case 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 AND CHOICE1 = 1 then 1
            else
            CHOICE2
            end,
CHOICE3 =
            case when CHOICE3 = 0 AND CHOICE1 = 1 AND CHOICE2 = 1 then 1
            else
            CHOICE3
            end,
CHOICE4 =
            case when CHOICE4 = 0 AND CHOICE1 = 1 AND CHOICE2 = 1 AND CHOICE3 = 1 then 1
            else
            CHOICE4
            end,
CHOICE5 =
            case when CHOICE5 = 0 AND CHOICE1 = 1 AND CHOICE2 = 1 AND CHOICE3 = 1 AND CHOICE4 = 1 then 1
            else
            CHOICE5
            end
from COO_CONVO_ATTENDANCE,
     coo_001_import_ids
where coo_convo_attendance.ID =  coo_001_import_ids.import_id

Open in new window


Thanks,
Chris

Author

Commented:
Thanks Chris

Commented:
Hello Doug - did it work out or is there still an issue?

Chris

Author

Commented:
yes it worked.  Thank you

Commented:
Excellent, please mark the question as answered.

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial