Can't understand why SQL table won't update on statement-CodePudding

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.

CodePudding user response：

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 values must satisfy both 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.

CodePudding user response：

What result are you getting if you run the below select statement?

SELECT a.question_name,
    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)) as SubQuery1,
    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))  as SubQuery2
    FROM acrl.ACRLData_Edited_Test a
    WHERE a.question_name LIKE '3a.%'

Does any of the two subquery columns return NULL value? If so, try enclosing it with ISNULL( cast(...),0) ISNULL( cast(...),0).