Updates values from column in SQL-CodePudding

Im trying to change the value from the Merchant_Category_Code column.

I tried CASE Statement but didn't work.

CASE
    WHEN MERCHANT_CATEGORY_CODE = 6051 THEN 'Quasi Cash–Merchant'
    WHEN MERCHANT_CATEGORY_CODE = 4829 THEN 'Wire Transfer Money Orders & Money Transfer'
    WHEN MERCHANT_CATEGORY_CODE = 6012 THEN 'Member Financial Institution–Merchandise And Services'
    WHEN MERCHANT_CATEGORY_CODE = 6011 THEN 'Member Financial Institution–Automated Cash Disbursements'
    WHEN MERCHANT_CATEGORY_CODE = 7372 THEN 'Computer Programming, Data Processing and Integrated System Design Services'
    WHEN MERCHANT_CATEGORY_CODE = 5812 THEN 'Eating Places and Restaurants'
    WHEN MERCHANT_CATEGORY_CODE = 5817 THEN 'Digital Goods: Applications (Excludes Games)'
    WHEN MERCHANT_CATEGORY_CODE = 3010 THEN 'Royal Dutch Airlines (KLM Airlines)'
    WHEN MERCHANT_CATEGORY_CODE = 3007 THEN 'Air France'
    WHEN MERCHANT_CATEGORY_CODE = 5735 THEN 'Record Shops'
    ELSE 'CHECK'
END AS MERCHANT_CATEGORY_CODE_DETAIL

Also,UPDATE but same result:

UPDATE "RED"."TRANSACTIONS"
SET    MERCHANT_CATEGORY_CODE=3035
      ,MERCHANT_CATEGORY_CODE='TAP Air Portugal (TAP)';

Here the screenshot from the table I want to change values:

Want to rename the values from this column

CodePudding user response：

Just a FYI --- this is a better way to to do this translation in SQL -- joins are faster than a case statement:

SELECT MERCHANGE_CATEGORY_CODE,
       COALESCE(T.DESCR,'CHECK') AS MERCHANT_CATEGORY_CODE_DETAIL
FROM SOMETABLE
LEFT JOIN (
  VALUES 
    (6051, 'Quasi Cash–Merchant'),`
    (4829, 'Wire Transfer Money Orders & Money Transfer'),
    (6012, 'Member Financial Institution–Merchandise And Services'),
    (6011, 'Member Financial Institution–Automated Cash Disbursements'),
    (7372, 'Computer Programming, Data Processing and Integrated System Design Services'),
    (5812, 'Eating Places and Restaurants'),
    (5817, 'Digital Goods: Applications (Excludes Games)'),
    (3010, 'Royal Dutch Airlines (KLM Airlines)'),
    (3007, 'Air France'),
    (5735, 'Record Shops')
) AS T(CODE,DESCR) ON SOMETABLE.MERCHANT_CATEGORY_CODE = T.CODE

CodePudding user response：

It doesn't make any sense to be attempting to assign string values to the MERCHANT_CATEGORY_CODE column given that it is some numeric type. Assuming you want to assign the description to a text column, you may update via a CASE expression as follows:

UPDATE "RED"."TRANSACTIONS"
SET MERCHANT_CATEGORY_CODE_DETAIL = CASE MERCHANT_CATEGORY_CODE
    WHEN 6051 THEN 'Quasi Cash–Merchant'
    WHEN 4829 THEN 'Wire Transfer Money Orders & Money Transfer'
    WHEN 6012 THEN 'Member Financial Institution–Merchandise And Services'
    WHEN 6011 THEN 'Member Financial Institution–Automated Cash Disbursements'
    WHEN 7372 THEN 'Computer Programming, Data Processing and Integrated System Design Services'
    WHEN 5812 THEN 'Eating Places and Restaurants'
    WHEN 5817 THEN 'Digital Goods: Applications (Excludes Games)'
    WHEN 3010 THEN 'Royal Dutch Airlines (KLM Airlines)'
    WHEN 3007 THEN 'Air France'
    WHEN 5735 THEN 'Record Shops'
    ELSE 'CHECK' END;