In a survey, a row with the question name 3a should equal the sum of the responses from the questions with the names 1a and 2a. This is the code I have:
UPDATE acrl.ACRLData_Edited_Test
SET question_textvalue =
cast(
(SELECT question_textvalue
FROM acrl.ACRLData_Edited_Test a2
WHERE a2.ipeds_id = a.ipeds_id
and a2.question_name = a.question_name and a2.question_name LIKE '1a.%'
) as decimal(10,2))
+ cast(
(SELECT question_textvalue
FROM acrl.ACRLData_Edited_Test a2
WHERE a2.ipeds_id = a.ipeds_id
and a2.question_name = a.question_name and a2.question_name LIKE '2a.%'
) as decimal(10,2))
FROM acrl.ACRLData_Edited_Test a
WHERE a.question_name LIKE '3a.%'
But it doesn't seem to work at all. When I run it, the survey responses/rows with the question 3a" still show Null, instead of the number.
For example, I have an entry with the 1a response as 4.7, the 2a response as 3.9 and the 3a response is NULL. It should be 8.6 in that scenario.
I see this in the nested query in the first case expression:
a2.question_name = a.question_name and a2.question_name LIKE '1a.%'
and also this in the final WHERE clause:
a.question_name LIKE '3a.%'
Logically, the a2.question_name = a.question_name condition means our question_name values must satisfy both WHERE clauses: the a.question_name restriction from the main outer WHERE clause and the a2.question_name restriction in the inner nested WHERE clause. It can only match records where the value is both LIKE '3a.%' and LIKE '1a.%'.
Those things cannot both be true, and therefore all the 3a.% records will become NULL even if they weren't before. The same issue applies to the second case expression as well.
Also, the . has specially meaning in a LIKE match and adds no value here. You may as well use LIKE '3a%', LIKE '1a%', etc, but LIKE '3a[.] %' is most exact.