How do I subtract from one column if a specific condition is met in MySQL?-CodePudding

I have a table with three columns

ID	Match_A	Match_B
ABC123	1	1
DEF111	0	1
QRS222	1	1

You can see ID 'ABC123' has a Match (as determined by 1) in both the Match_A and Match_B column. If their is a 1 in both of those columns, I am needing to write a formula that changes at least one of those columns into a 0. It doesn't matter if it's Match_A or Match_B.

The output for this ID would then turn into this.

ID	Match_A	Match_B
ABC123	1	0

Essentially, a match can happen in both columns, but for this report managers do not want it to be counted twice so one column has to be changed to 0 if a situation like this happens.

Any help is appreciated here! Thank you!

CodePudding user response：

So a CASE should get you to the result I think you want

SELECT unique_id, match_a, 
        case when match_a = 1 AND match_a = match_b
            then 0
            else match_b 
        end as match_b
from table