CODEWITHSUNDEEP
sql
user1318132
user1318132

Reputation: 15

Can these three update statements be combined?

I would like to be able to combine these three update statements, so I don't have to run them individually. I would typically enter multiple "products" and multiple "baskets".

The idea is to be able to update two fields based on "fieldB" being < '5' or "fieldB" between '5' and '10' or "fieldB" > '10' without running three separate queries.

Any assistance is much appreciated.

UPDATE table
set fieldA = round(8.7 * fieldA,0),
    fieldB = round(8.7 * fieldB,0)                                                                               
where product in ('APPLE','BANANA')
and basket IN ('BROWN BASKET','RED BASKET','YELLOW BASKET','BLUE BASKET')
and fieldB < '5';

UPDATE table
set fieldA = round(4.3 * fieldA,0),
    fieldB = round(4.3 * fieldB,0)                                                                               
where product in ('APPLE','BANANA')
and basket IN ('BROWN BASKET','RED BASKET','YELLOW BASKET','BLUE BASKET')
and fieldB between '5' and '10';

UPDATE table
set fieldA = round(2.5 * fieldA,0),
    fieldB = round(2.5 * fieldB,0)                                                                               
where product in ('APPLE','BANANA')
and basket IN ('BROWN BASKET','RED BASKET','YELLOW BASKET','BLUE BASKET')
and fieldB > '10';

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I think the intention that you want is this:

UPDATE table
    set fieldA = round((case when fieldB < '5' then 8.7 * fieldA
                             when fieldB between '5' and '10' then 4.3 * FieldA
                             when fieldB > '10' then 2.5 * fieldA
                        end), 0),
        fieldB = round((case when fieldB < '5' then 8.7 * fieldB
                             when fieldB between '5' and '10' then 4.3 * FieldB
                             when fieldB > '10' then 2.5 * fieldB
                        end), 0)                                                                               
    where product in ('APPLE', 'BANANA') and
          basket IN ('BROWN BASKET', 'RED BASKET', 'YELLOW BASKET', 'BLUE BASKET');

However, this is not exactly what your original updates do. The problem is that you are resetting the value for fieldB in each update. So, if FieldB is 1 initially, it will then get set to 8.7 * FieldB = 8.7. This will then be caught by the second update and so on.

If that cascading is the intention, then the arithmetic would be slightly different. However, the idea behind the conditional updates would be the same.

Upvotes: 2

Hart CO
Hart CO

Reputation: 34774

You can use CASE statements, syntax may depend on RDBMS:

UPDATE table
SET fieldA = CASE WHEN fieldB < '5' THEN round(8.7 * fieldA,0)
                  WHEN fieldB BETWEEN '5' AND '10' THEN round(4.3 * fieldA,0)
                  WHEN fieldB > '10' THEN round(2.5 * fieldA,0)
             END
    ,fieldB = CASE WHEN fieldB < '5' THEN round(8.7 * fieldB,0) 
                  WHEN fieldB BETWEEN '5' AND '10' THEN round(4.3 * fieldB,0) 
                  WHEN fieldB > '10' THEN round(2.5 * fieldB,0)  
             END                                                                            
where product in ('APPLE','BANANA')
and basket IN ('BROWN BASKET','RED BASKET','YELLOW BASKET','BLUE BASKET')

Upvotes: 3

Related Questions