CODEWITHSUNDEEP
sqlsql-server
TtT23
TtT23

Reputation: 7030

SQL Case Update to two different column based on select

I'm trying to perform what I believe a very simple case-update SQL to two different column based on a select:

PROD_TB:

Product_Code   Reg_Price     Sale_Price
     A           1000           2000

PRICE_TB:

Product_Code   Type    Price
     A         REG     3000
     A         SALE    4000

Desired update result:

PROD_TB:

Product_Code   Reg_Price     Sale_Price
     A           3000           4000

What I attempted:

UPDATE PROD_TB
SET Reg_Price = CASE 
                   WHEN PRICE_TB.Type = 'REG' 
                     THEN PRICE_TB.Price 
                     ELSE Reg_Price 
                END, 
    Sale_Price = CASE 
                    WHEN PRICE_TB.Type = 'SALE' 
                      THEN PRICE_TB.Price 
                      ELSE Sale_Price 
                 END
FROM 
    PROD_TB
JOIN 
    PRICE_TB ON PROD_TB.PRODUCT_CODE = PRICE_TB.PRODUCT_CODE

Running the above SQL only updates regular price, not the sale price. Does SQL not support these types of update query? Or did I make an elementary mistake?

Upvotes: 1

Views: 93

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

If your table PRICE_TB doesn't always contain both values, you could use this to make sure the table is updated anyway:

UPDATE t1
SET 
  Reg_Price = coalesce(t2.Price, t1.Reg_Price),
  Sale_Price = coalesce(t3.Price, t1.Sale_Price)
FROM PROD_TB t1
LEFT JOIN
  PRICE_TB t2
ON 
  t1.ProductCode = t2.ProductCode AND
  t2.[Type] = 'REG'
LEFT JOIN
  PRICE_TB t3
ON 
  t1.ProductCode = t3.ProductCode AND
  t3.[Type] = 'SALE'
WHERE 
  t2.[Type] = 'REG' OR
  t3.[Type] = 'SALE'

Upvotes: 0

James Z
James Z

Reputation: 12318

You need to get the data into one row first, here's example that fetches always the biggest price, in case there's more than one in PRICE_TB, otherwise it should work the same way as @Kahn's sql.

UPDATE 
  PROD
SET 
  PROD.Reg_Price = PRICE.Reg_Price,
  PROD.Sales_Price = PRICE.Sales_Price
FROM PROD_TB PROD, cross apply (
  select 
    max(CASE WHEN Type = 'REG' THEN Price ELSE 0 end) as Reg_Price,
    max(CASE WHEN Type = 'SALE' THEN Price ELSE 0 end) as Sale_Price
  from
    PRICE_TB PRICE
  where
    PRICE.Product_Code = PROD.Product_Code
) PRICE

Upvotes: 1

Kahn
Kahn

Reputation: 1660

Something like this? Basically, just join the set from the PRICE_TB on the condition of which column you want to get from it.

But this is assuming you will only ever have one PRICE_TB.TYPE per updated PROD_TB column name, and that each PROD_TB column always contains a value in PRICE_TB, otherwise it'll be NULL and that row won't be returned. So make sure you know the variations of data that can exist here. 

UPDATE PROD
SET Reg_Price = REG.Price, Sale_Price = SALE.Price
FROM PROD_TB PROD
JOIN PRICE_TB REG ON REG.Product_Code = PROD.Product_Code AND REG.Type = 'REG'
JOIN PRICE_TB SALE ON SALE.Product_Code = PROD.Product_Code AND SALE.Type = 'SALE'

Upvotes: 6

Related Questions