Update a row in oracle SQL that has been copied from the same table without effecting previous row

Right now my table(Batch_relations) has fields such as (Batch_name, Batch_size,…)

I want to be able to create a copy of a row from this table(already done) and then update that row without changing the row that is was copied from.

For example, lets say I have a batch_name called testBatch and the batch size is 3. After copying it, I will have two rows, both with the name testBatch and a batch size of 3. It will look somethign like this.

If I want to add 2 to one of the batches, so that now the rows will be (If one batch is larger than the other, we want to update the batch that has a larger value)

how would I acomplish that?

This is my current SQL querry

UPDATE BATCH_RELATION
SET BATCH_SIZE = ?
WHERE (BATCH_NAME, BATCH_SIZE) = (
    SELECT BATCH_NAME, BATCH_SIZE
    FROM (
        SELECT BATCH_NAME, BATCH_SIZE
        FROM BATCH_RUN_RELATION
        WHERE BATCH_NAME = ?
        ORDER BY BATCH_SIZE DESC, ROWID
    ) 
    WHERE ROWNUM = 1
)

After this is run, it will change both values so that the table will look like this instead

What query could I use to fix this issue

>Solution :

merge is one option.

Before:

SQL> select * From test order by batch_name, batch_size;

BATCH_NAM BATCH_SIZE
--------- ----------
Batch2             8
testBatch          3
testBatch          3

Update batch_size for one of testBatch rows:

SQL> merge into test a
  2    using (select batch_name,
  3             max(rowid) as rid
  4           from test
  5           group by batch_name
  6          ) b
  7    on (a.batch_name = b.batch_name)
  8    when matched then update set
  9      a.batch_size = 5
 10      where a.rowid = b.rid
 11        and a.batch_name = 'testBatch';

1 row merged.

After:

SQL> select * From test order by batch_name, batch_size;

BATCH_NAM BATCH_SIZE
--------- ----------
Batch2             8
testBatch          3
testBatch          5          --> here it is

SQL>