I have a database that contains the following fields,
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.
case when ACADEMIC1 = 0 then 1
when ACADEMIC1 = 1 and ACADEMIC2 = 0 and ACADEMIC_DEFICIENT > 0 then 1
case when CHOICE1 = 0 then 1
case when CHOICE2 = 0 then 1
case when CHOICE3 = 0 then 1
case when CHOICE4 = 0 then 1
case when CHOICE5 = 0 then 1
where coo_convo_attendance.ID = coo_001_import_ids.import_id