CODEWITHSUNDEEP
mysqlsqlsql-updatecase
ma77c
ma77c

Reputation: 1086

MYSQL - UPDATE multiple fields based on single CASE statement

OBJECTIVE

I am looking for a way to update one or both fields using the same CASE statement

UPDATE vendor
    SET special_cost =
    (
        CASE
            WHEN
                cost > 14
            THEN
                14
            ELSE
                SET special_cost = cost, cost = 14
        END
    )    
WHERE upc = '12345678912345'

LOGIC

This is some logic that explains what I want to happen above.

if ($cost > 14) {
    $special_cost = 14;
} else {
    $special_cost = $cost;
    $cost = 14;
}

It does not seem that there is a way to do this with a single CASE statement. Perhaps, this can be done with the mysql IF statement?

Upvotes: 1

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You are referring to case expressions not case statements.

You appear to want something like this:

UPDATE vendor
    SET special_cost = GREATEST(cost, 14),
        cost = 14
    WHERE upc = '12345678912345';

Upvotes: 2

Related Questions