1

Given the table named ROLE:

role_id[PK] NUMBER
role_name   VARCHAR2

Using only SQL (no PL/SQL) how would I write a MERGE (or UPDATE?) statement that would update the role_name as below, but only for rows where another row with the new role_name does not already exist? (By "new role_name" I mean 'prefix-' || role_name)

UPDATE role
SET role_name = 'prefix-' || role_name
WHERE role_name LIKE 'com.example.%';

2 Answers 2

2

Add the predicate:

 AND NOT EXISTS (
 SELECT 1 FROM role AS r_existing 
 WHERE r_existing.role_name = 'prefix-' || role.role_name)
2
  • Is not exists better than not in? Or are they equivalent? Jan 2, 2021 at 14:25
  • 1
    They’re nearly equivalent, depending on conditions. Ideally they get executed the same way. Null values can confuse matters though, so I prefer “not exists”.
    – Rob Farley
    Jan 2, 2021 at 23:35
0

Here's what I was able to manage:

MERGE INTO ROLE A USING
 (SELECT
      role_id,
      role_name,
      'prefix-' || role_name as new_role_name
  FROM ROLE r
  WHERE role_name LIKE 'com.example.%'
    AND not exists (
        SELECT 1
        FROM ROLE r2
        WHERE r2.role_name = 'prefix-' || r.role_name)
  ) B
ON (A.ROLE_id = B.ROLE_id)
WHEN MATCHED THEN
UPDATE SET 
  A.ROLE_NAME = B.NEW_ROLE_NAME;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.