Compare values in a row of three columns and replace with NULL if equal

user_14675230 0
Viewed 128

See the table below:

Price_one Price_two Price_three Price_four
204 204 204 204
208 208 208 208
212 212 212 212
206 195 187 204

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:

Price_one Price_two Price_three Price_four
204 NULL NULL NULL
208 NULL NULL NULL
212 NULL NULL NULL
206 195 187 204

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?

sql-server sql
1 Answers
;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;

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;
user_613050
Related