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?
>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;
- 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