What's the best way to replace a value in column A based on the value of column B in SQL?-CodePudding

I have two columns, column A contains the state and column B contains the city. A majority of the mapping is correct but for some reason 'San Diego' in column B is mapped to 'AZ' in column A when it should be 'CA' in column A. Below is what I thought I could do but doesn't seem to work. I figure the answer is a CTE or subquery but would love to know if there is a cool alternative way. Thanks!

SELECT
column B,
CASE
   WHEN column B = 'San Diego'
   THEN column A = 'CA'
   ELSE column A
END AS fix
FROM doesnotmatter

CodePudding user response：

Looks like your code would work to me with just one tweak. In the 'Then' part of the case statement you don't need to reference column A, as this case statement is in a sense becoming your new column A.

SELECT
column B,
CASE
   WHEN column B = 'San Diego'
   THEN 'CA'
   ELSE column A
END AS column A
FROM doesnotmatter

CodePudding user response：

Seems to be a mere syntax issue:

SELECT
  column_B,
  CASE WHEN column_B = 'San Diego'
    THEN 'CA'
    ELSE column_A
  END AS fix
FROM doesnotmatter