Is there a way to do multiple things in a CASE statement THEN section using MYSQL?

Question

I'm new to SQL and MYSQL and am trying to set a value in a UPDATE statement using a CASE statement. I am trying to do two things in the THEN section of the CASE statement and cannot figure out if it's a syntax problem or not possible to do two things in the THEN section.

I have looked up these two question and have not been able to connect these two questions to what I am trying to do:

Multiple THEN to a single CASE statement?

update mysql with if condition

My Current Attempt

UPDATE offering, tmpTable
SET offering.offNumEnrolled = CASE WHEN offering.OffNumEnrolled = 0 
                              THEN (tmpTable.stdTotal SET aRowsAltered = aRowsAltered + 1) 
                              ELSE (offering.offNumEnrolled) END
WHERE [some condition]

To better illustrate what I am trying to accomplish here is this code:

UPDATE offering, tmpTable
SET offering.offNumEnrolled = IF (offering.OffNumEnrolled = 0) THEN
                                    (SET TO THIS tmpTable.stdTotal AND 
                                    SET aRowsAltered = aRowsAltered + 1)
                                 ELSE
                                    SET offering.offNumEnrolled;
                                 END IF;
WHERE [some condition]

What would be the proper way of setting the value and incrementing a counter if the row was changed?

why do you want rowsaltered if it cant get via `sql%rowcount`, just update the table!!! — Nikhil S, Apr 28 '19 at 05:10

score 1 · Accepted Answer · answered Apr 28 '19 at 11:44

I think you want something like this:

UPDATE offering o JOIN
       tmpTable tt
       ON  <join conditions here>
    SET o.offNumEnrolled = tt.stdTotal, 
        ?.aRowsAltered = ?.aRowsAltered + 1
    WHERE o.OffNumEnrolled = 0;

The ? is the table alias for wherever aRowsAltered comes from. It is probably optional in this case, but a good practice in general to qualify column names.

Notes:

MySQL supports JOIN in the UPDATE clause. Use it.
The JOIN conditions should go in an ON clause.
Table aliases make the query easier to write and to read.
You only want to update certain rows, so the condition on o.OffNumEnrolled = 0 goes in the WHERE clause.
MySQL allows you to update multiple tables at once, so aRowsAltered can be modified regardless of which table it is in .

Is there a way to do multiple things in a CASE statement THEN section using MYSQL?

1 Answers1