SQL UPDATE WHERE CASE

I know my SQL knowledge is weak. Unfortunately I found out I know less than I had perviously thought.

I wrote an UPDATE command to use in a simple HTML settings page using a form. (There is some browser side validation and also more server side validation of the form data before the data reaches the UPDATE SQL).

My goal is to updated 1 column in 2 rows. Nothing else. But I was wrong. It updated the two target rows with the values expected. The SQL also NULLed the column of all values in all the other rows.

My question 1: What caused this in my SQL?? My question 2: Is there a better way of performing this update??

<cfquery datasource="OurDBLocation" name="changeValues">
    UPDATE sometable
    SET rowvalueB = CASE
    WHEN rowvalueA = 'A123' THEN #val(FORM.a123value)#
    WHEN rowvalueB = 'A456' THEN #val(FORM.a456value)#
    END
    OUTPUT INSERTED.*
    WHERE rowvalueA IN ('A123','A456')
</cfquery>

NOTE: This is an internal form accessible only to developers with VPN access to our code base. We use Lucee (aka ColdFusion) on the back end for those that do not understand the #val(FORM.a123value)#

Answer

EDIT: 2020-10-07

Per latest comments, if you want to reduce the number of UPDATEs, especially when it’s not changing the value but still resulting in a write, you could use a CTE to filter only the rows you need to update and then work with just those.

<cfquery datasource="OurDBLocation" name="changeValues">
; WITH cte AS (
    SELECT rowvalueB
    FROM sometable
    WHERE rowvalueA = 'A123'
        OR ( rowvalueA = 'A456' AND rowvalueB = 'A456' )
)
UPDATE cte
SET rowvalueB =
        CASE
            WHEN rowvalueA = 'A123' THEN <cfqueryparam value="#val(FORM.a123value)#" cfsqltype="cf_sql_integer">
            ELSE <cfqueryparam value="#val(FORM.a456value)#" cfsqltype="cf_sql_integer">
        END
    OUTPUT INSERTED.*
;
</cfquery>

CTE UPDATEs are pretty awesome, if they are available in your flavor of SQL (and by marking tsql, I’m guessing you’re SQL Server). Test it to make sure it works for your case. I don’t know why a cfquery wouldn’t run a CTE UPDATE properly, but I’m not able to test it right now.

In explanation of what is happening: ;WITH cte AS.. creates the named CTE data set. Also see https://www.sqlshack.com/sql-server-common-table-expressions-cte/ SELECT... defines the data that makes up the CTE. Since you want to filter for rowvalueA IN (A123,A456) but then update different values, I simplified the WHERE statement. You’ll only update with FORM.a456 value when both rowvalueB AND rowvalueA = ‘A456’. So the CTE filters out all rows where rowvalueA=A456 but rowvalueB is something other than A456. Those would be the records that were getting reset to NULL originally. Then you use a normal UPDATE statement to update the CTE, which will pipe up to the actual rows from the table. Since you’re still using a conditional value in the update, we have to use the CASE...WHEN... but the rows have already been filtered and simplified. We only need to check if rowvalueA=A123.

Also note that when I mentioned validation on the client, I wasn’t saying to not do it. I was simply pointing out that client-side validation is trivial to bypass, so shouldn’t be relied on for any sort of validation that is intended for anything more than client display or ease of use.

========== ORIGINAL ANSWER =======================

If I correctly understand your intent, your query appears to be mostly correct. You are only updating rowdataB in your table. But you aren’t giving a default value if your WHEN conditions aren’t met, so NULL is assigned to it.

<cfquery datasource="OurDBLocation" name="changeValues">
    UPDATE sometable
    SET rowvalueB = 
        CASE
            WHEN rowvalueA = 'A123' THEN <cfqueryparam value="#val(FORM.a123value)#" cfsqltype="cf_sql_integer">
            WHEN rowvalueB = 'A456' THEN <cfqueryparam value="#val(FORM.a456value)#" cfsqltype="cf_sql_integer">
            ELSE rowvalueB
        END
    OUTPUT INSERTED.*
    WHERE rowvalueA IN ('A123','A456')
</cfquery>

This just reassigns the value of rowvalueB back to itself if none of your WHEN conditions in your CASE are met. Because of the WHERE clause, it will ignore any other row.

I think this might add the row to your OUTPUT INSERTED.* if rowvalueA is 'A456', since it will match in your WHERE clause but not in your WHEN clause. If that’s not what you’re after, you might want to change your WHERE.