Oracle: Safely change value(s) in table, if not new entry already exists, remove the old one

Question

I have a table looking like this

table, th, td {
  border: 1px solid black;
  border-collapse: collapse;
}

th, td {
  padding: 2px;
}

th {
  text-align: left;
}

<table style="width:40%">
  <tr>
    <th>COL_1[PK]</th>
    <th>COL_2[PK]</th>
    <th>COL_3</th>
  </tr>
  <tr>
    <td>A</td>
    <td>Bar</td>
    <td>4</td>
  </tr>
  <tr>
    <td>B</td>
    <td>Foo</td>
    <td>8</td>
  </tr>
  <tr>
    <td>C</td>
    <td>Foo</td>
    <td>15</td>
  </tr>
  <tr>
    <td>C</td>
    <td>NewFoo</td>
    <td>15</td>
  </tr>
  <tr>
    <td>D</td>
    <td>Foo</td>
    <td>16</td>
  </tr>
  <tr>
    <td>E</td>
    <td>Bar</td>
    <td>23</td>
  </tr>
  <tr>
    <td>...</td>
    <td>...</td>
    <td>...</td>
  </tr>
</table>

I want to change all rows where COL_2 = 'Foo' to NewFoo. The problem is that some rows are already changed without having the old value removed. So if I run the command

update TABLE
set COL_2 = 'NewFoo'
where COL_2 = 'Foo'

However, I will run into trouble when I try to change the third row (C, Foo, 15) to (C, NewFoo, 15) since that entry already exists.

One solution to this is to simply delete all rows that have already been changed before and convert them again:

delete from TABLE where COL_2 = 'NewFoo';
update table
set COL_2 = 'NewFoo'
where COL_2 = 'Foo';

This will work fine until someone runs the script twice on the same table. The first time all the old converts will be removed and all the 'Foo' values will be converted to 'NewFoo' (as desired). The second time the script is executed all the converted records will be deleted.

I am looking for a way to do this without having to worry about someone running the script twice. I'm thinking the solution will look like something in these examples but I cannot figure out how to form the statement

Oracle insert if not exists statement

Oracle: how to INSERT if a row doesn't exist

Answer 1

Use an EXISTS clause in your update to rule out the existence of the new update record:

UPDATE yourTable t1
SET COL_2 = 'NewFoo'
WHERE
    COL_2 = 'Foo' AND
    NOT EXISTS (SELECT 1 FROM yourTable t2
                WHERE t2.COL_3 = t1.COL_3 AND t2.COL_1 = t1.COL_1 AND t2.COL_2 = 'NewFoo');

Answer 2

I found a soltion (Tim Biegeleisen's reply led me in the right direction.)

This will work and is save to run multiple times

UPDATE table_name as t1
SET COL_2 = 'NewFoo'
WHERE
    COL_2 = 'Foo' AND
    NOT EXISTS (SELECT 1 FROM table_name as t2 WHERE t2.COL_1 = t1.COL_1 AND t2.COL_2 = 'NewFoo');

delete from table_name
WHERE COL_2 = 'Foo';