Modify Column Value Based on Some Conditions
Given the Following Table: .. . | Item | Type |. .. .. . | a | 1 |. .. .. . | B | 2 |. .. .. . | a | 2 |. .. .. . | a | 3 |. .. .. . | B | 3 |. .. .. . | a | 2...
Given the following table:
... | item | type | ...
... | A | 1 | ...
... | B | 2 | ...
... | A | 2 | ...
... | A | 3 | ...
... | B | 3 | ...
... | A | 2 | ...
Let's call items by pairings (A1, B2, A2, etc). There is an error in the table where an A3 should actually be C3.
I want to write a query that will return rows and change the item column to C if the row returned A3.
Example query that doesn't work:
SELECT (item like 'A' ? (type == 3? 'C' : 'A') : item) as "item", type from table;
My desired output would be:
"item" | type
A | 1
B | 2
A | 2
C | 3
B | 3
A | 2
What is the correct way of doing this? If possible?
1 Answer
update table tablename
set item='C'
where type=3 and item='A'
If you don't need updating these values in the table, but to get replaced values them as a result of the select, then
select
case when item='A' and type=3 then 'C'
else item
end as newitem, type from tablename
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
