Oracle – Update Row Where Associated Row Does Not Exist

mergeoracle

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.%';

Best Answer

Add the predicate:

 AND NOT EXISTS (
 SELECT 1 FROM role AS r_existing 
 WHERE r_existing.role_name = 'prefix-' || role.role_name)

Related Solutions

Ms-access – Merge Fields into one Row with a Query

If you have not more than 2 values for domain, this will help:

SELECT [DOMAIN], IIF(First([Q32])<>Last([Q32]), First([Q32])+' '+Last([Q32]),First(Q32)) AS _Q32
FROM [table]
GROUP BY [DOMAIN];

SQL Server MERGE with Update Source – How to Handle Non-Matching Targets

Updates are tracked by MERGE by providing:

the $Action value UPDATE;
the old values in the memory table deleted; and
the new values in the memory table inserted.

Therefore both in the case where a match is found, and in the case where there is no match by target, the values of the identity column is available in the inserted memory table.

Best Answer

Related Solutions

Ms-access – Merge Fields into one Row with a Query

SQL Server MERGE with Update Source – How to Handle Non-Matching Targets

Related Question