Oracle Database 11.2.0.4
I've got a table which has a GUID based primary key. In a PL/SQL package I am doing some processing which results in rows of that table being updated. As an example, let's say my table looks like:
create table my_test_table (id varchar2(32), colour varchar2(100));
insert into my_test_table (id, colour) values ('one', 'red');
insert into my_test_table (id, colour) values ('two', 'green');
insert into my_test_table (id, colour) values ('three', 'blue');
insert into my_test_table (id, colour) values ('four', 'green');
insert into my_test_table (id, colour) values ('five', 'yellow');
commit;
In the package, I want to switch colours 'green' for 'blue' and 'blue' for 'green'.
So I want to end up with:
'one', 'red'
'two', 'blue'
'three', 'green'
'four', 'blue'
'five', 'yellow'
I can't do it in a single update statement as some other processing happens for each row.
If I run this:
update my_test_table set colour = 'blue' where colour = 'green';
update my_test_table set colour = 'green' where colour = 'blue';
Then I would end up with this:
'one', 'red'
'two', 'green'
'three', 'green'
'four', 'green'
'five', 'yellow'
...which isn't what I want as I end up updating 3 rows to 'blue' rather than '2'.
To get around this, I was thinking of keeping track of the Ids which I've already updated and then only updating the colour of a row if that row hasn't already been updated in this session.
I have setup some PL/SQL code to do this using an array as "table of varchar2". For each row I update I add the id to the array. Then I only update rows where the id isn't in that array. I've pretty much got that working as a concept and can post the sample code here for comment.
But first I'm wondering if I am over complicating this?
Is there any other safe way of swapping values when not using a single Update statement?
Thanks,
John