UPDATE 2 rows if both are equal without a SELECT-CodePudding

My old table was like this:

ID  | A | B |
---- --- --- 
112 | 2 | 2 |
345 | 3 | 0 |

And now it's like this:

ID  | type | value |
---- ------ ------- 
112 | A    | 2     |
112 | B    | 2     |
345 | A    | 3     |
345 | B    | 0     |

So, updating (for example) A and B if both were equal for a certain ID used to be simple without a select. But in this new type of table, is there a way to achieve the same thing?

In case it's not possible, doing an INSERT ... ON DUPLICATE KEY UPDATE have anything i could use to make it possible? (even if it's a bad practice, this is just for testing purposes!)

CodePudding user response：

I think something like this will do what you're after:

update someTable set value = 
    case `type` 
      when 'A' then 99
      when 'B' then 101
      else `value`
    end 
    where id = 112;

However, I'm not sure your example is a good one. Based on that I'd probably use the first format.

CodePudding user response：

This should update any rows which have the same value and same ID to 5. You may replace the SET statement with any rows or values you wish.

UPDATE new
SET value=5 
WHERE ID IN (
    SELECT ID as outerID 
    FROM new 
    WHERE (
        SELECT COUNT(DISTINCT value)
        FROM new 
        WHERE ID=outerID
    ) = 1
);

I'm not sure if sub-queries are against your "no select" rule, but as far as I can tell you will need a select to do the sort of comparison required for this. This will also only work where ALL the rows value colums are the same, not just ANY that are the same.