MYSQL - UPDATE multiple fields based on single CASE statement

Question

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?

What is the logic supposed to be doing? It doesn't make sense to me. Sample data and desired results help. — Gordon Linoff
– Gordon Linoff, Commented Feb 1, 2018 at 18:42
@GordonLinoff I updated the question to reflect the intended logic — ma77c
– ma77c, Commented Feb 1, 2018 at 18:47

Gordon Linoff · Accepted Answer · 2018-02-01 18:43:34Z

2

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';

answered Feb 1, 2018 at 18:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MYSQL - UPDATE multiple fields based on single CASE statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related