See the table below:

My data provider does this weird thing that if the price is unavailable it just copies the value from Price_one to the rest of the columns. What I want to do is to replace the equal rows of Price_two, Price_three, and Price_four with just NULLs, so that I would get the refurbished table below:

So Price_one stays and the rows with unequal values also stay. It is essential that in order for the value to be replaced by NULL, all three prices need to be equal to each other; if just two are, then they should remain so. Is that feasible in SQL?

CodePudding user response：

;WITH cte AS
(
  SELECT * FROM dbo.tablename
    WHERE Price_one   = Price_two
      AND Price_two   = Price_three
      AND Price_three = Price_four
)
UPDATE cte SET Price_two   = NULL, 
               Price_three = NULL, 
               Price_four  = NULL;

• Example db<>fiddle

I like the CTE approach because, for more complex queries, it makes it very easy to replace the DML action with a SELECT to be sure you're affecting the right rows. In this situation we can also just write a direct UPDATE:

UPDATE dbo.tablename
  SET Price_two   = NULL, 
      Price_three = NULL, 
      Price_four  = NULL
    WHERE Price_one   = Price_two
      AND Price_two   = Price_three
      AND Price_three = Price_four;

• Example db<>fiddle

CodePudding user response：

If it is a select query you can write it like so:

select price_one
     , case when distinct_prices > 1 then price_two end
     , case when distinct_prices > 1 then price_three end
     , case when distinct_prices > 1 then price_four end
from t
cross apply (
    select count(distinct price) from (values (price_one), (price_two), (price_three), (price_four)) as va(price)
) as ca(distinct_prices)

or this:

select price_one
     , case when combine is null then price_two end
     , case when combine is null then price_three end
     , case when combine is null then price_four end
from t
cross apply (
    select case when price_one = price_two and price_one = price_three and price_one = price_four then 1 end
) as ca(combine)