doug-t
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
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_
ASKER
Thanks jogos, that was my next direction if there wasn't another way.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Chris
Hello Doug - did it work out or is there still an issue?
Chris
Chris
ASKER
yes it worked. Thank you
Excellent, please mark the question as answered.
Chris
Chris
Not possible in one statement without including testing in each next update if the previous statement are already met.