Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

;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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading