I have to check if field COUNTRY = 'England', if yes then I have to update FIELD_VALUE for FIELD_NAME CITY to 'London', FIRST_NAME to 'John', LAST_NAME to 'Doe' for same ID that COUNTRY has.

What would be the best way to do this? I have around 1000 IDs with different FIELD_VALUE for FIELD_NAME = COUNTRY.

I'm working on someone else DB and I can't change design of this table.

CodePudding user response：

You could use:

UPDATE yourTable
SET CITY = 'London', FIRST_NAME = 'John', LAST_NAME = 'Doe'
WHERE ID IN (SELECT ID FROM yourTable WHERE COUNTRY = 'England');

But as the comments have mentioned, a more normalized design might be a lot easier to use.

CodePudding user response：

You can use an updatable CTE

WITH cte AS (
    SELECT *,
      IsCtryEngl = COUNT(CASE WHEN FIELD_NAME = 'COUNTRY' AND FIELD_VALUE = 'England' THEN 1 END)
                      OVER (PARTITION BY ID)
    FROM YourTable
    WHERE FIELD_NAME IN ('COUNTRY', 'FIRST_NAME', 'LAST_NAME', 'CITY')
)
UPDATE cte
SET
  FIELD_VALUE = CASE FIELD_NAME
                WHEN 'FIRST_NAME' THEN 'John'
                WHEN 'LAST_NAME'  THEN 'Doe'
                WHEN 'CITY'       THEN 'London'
                END
WHERE IsCtryEngl > 0
  AND FIELD_NAME IN ('FIRST_NAME', 'LAST_NAME', 'CITY');

As mentioned, this is obviously much easier with a normalized design

CodePudding user response：

With using only update:

UPDATE T 
SET FIELD_VALUE = CASE 
                      WHEN T.FIELD_NAME = 'CITY' THEN 'London'
                      WHEN T.FIELD_NAME = 'FIRST_NAME' THEN 'John'
                      WHEN T.FIELD_NAME = 'LAST_NAME' THEN 'Doe'
                      ELSE T.FIELD_VALUE
                  END
FROM T 
INNER JOIN T AS T1 ON (T.ID = T1.ID)
WHERE T1.FIELD_NAME = 'COUNTRY' 
  AND T1.FIELD_VALUE = 'England' 
  AND T.FIELD_NAME IN ('CITY', 'FIRST_NAME', 'LAST_NAME')