How do you write a pl sql script that iterates finds a previous version of all content objects and updates a property value on each?

Question

I have a database that looks like :

CREATE TABLE content (contentid int, version int, versioncomment varchar(512), prevver int);
insert into content values (101,1, null, null);
insert into content values (102,2, null, 101);
insert into content values (100,3, 'Migrated', 102);


insert into content values (201,1, null, null);
insert into content values (202,2, null, 201);
insert into content values (200,3, 'Migrated', 202);


insert into content values (301,1, null, null);
insert into content values (302,2, null, 301);
insert into content values (300,3, '', 302);

CREATE TABLE os_prop (entity_id int, entity_key varchar(512), text_val varchar (512));
insert into  os_prop values(100, 'My.Key','value100');
insert into  os_prop values(101, 'My.Key','value101');
insert into  os_prop values (102, 'My.Key',null); <====== PROBLEM !
insert into  os_prop values(101, 'Random.Key','randomValue101');
insert into  os_prop values (100, 'Random.Key','randomValue100');

insert into  os_prop values(200, 'My.Key','value200');
insert into  os_prop values(201, 'My.Key','value201');
insert into  os_prop values(202, 'My.Key',null); <====== PROBLEM !
insert into  os_prop values(201, 'Random.Key','randomValue201');
insert into  os_prop values(200, 'Random.Key','randomValue200');

// no problem here as nothing has been migrated. leave it as is
insert into  os_prop values(300, 'My.Key','value300');
insert into  os_prop values(301, 'My.Key','value301');
insert into  os_prop values(302, 'My.Key','value302');

and I need it to look like:

http://sqlfiddle.com/#!4/21151/1

CREATE TABLE content (contentid int, version int, versioncomment varchar(512), prevver int);
insert into content values (101,1, null, null);
insert into content values (102,2, null, 101);
insert into content values (100,3, 'Migrated', 102);


insert into content values (201,1, null, null);
insert into content values (202,2, null, 201);
insert into content values (200,3, 'Migrated', 202);


insert into content values (301,1, null, null);
insert into content values (302,2, null, 301);
insert into content values (300,3, '', 302);

CREATE TABLE os_prop (entity_id int, entity_key varchar(512), text_val varchar (512));
insert into  os_prop values(100, 'My.Key','value100');
insert into  os_prop values(101, 'My.Key','value101');
insert into  os_prop values (102, 'My.Key','value100'); <===== FIXED
insert into  os_prop values(101, 'Random.Key','randomValue101');
insert into  os_prop values (100, 'Random.Key','randomValue100');

insert into  os_prop values(200, 'My.Key','value200');
insert into  os_prop values(201, 'My.Key','value201');
insert into  os_prop values(202, 'My.Key','value200'); <===== FIXED 
insert into  os_prop values(201, 'Random.Key','randomValue201');
insert into  os_prop values(200, 'Random.Key','randomValue200');

// untouched
insert into  os_prop values(300, 'My.Key','value300');
insert into  os_prop values(301, 'My.Key','value301');
insert into  os_prop values(302, 'My.Key','value302');

The examples above is simplified to distill my question but in reality we have a database that contains thousands of contentids with multiple versions or perhaps only 1 version (some migrated and some not migrated). Therefore this has to be done with a PL/SQL script (perhaps using cursors).

In a nutshell when a database migration occurs we have these "My.Key" values in os_prop table being lost for all previous versions of a migrated contentid. So the goal is to copy back the "My.Key" value of the migrated content to it's previous version. A null value for "My.Key" value is unacceptable.

So I'm not an expert a PL/SQL but I know I need to do something like this:

// step 1 find list of contentids that have the word "Migrated" in their comment: 
contentids = select contentids c, version v, prevver pv from content where versioncomment like '%Migrated%'; 


// step 2 for each contentid listed in step 1 find its corresponding "My.Key" value from os_prop
loop
select text_val from os_prop where entity_id = <contentid from step 1>

// step 3 need to figure out the previous version's contentID of each "Migrated" content found in step 1

if pv is null
select contentid where prevver = c and version = v-1
else
select contentid where prevver=pv and version = v-1

// step 4 finally need to update text_val to the same value as migrated version's
update os_prop set text_val = <text_vals from step 2>where entity_id = <prev version from step 3>

@gfrobenius -in step 3 i am trying to figure out the content id of the previous version of all migrated pages. In the SQLFiddle table I created I made them simple numbers like 100,101,102 etc.. But in reality they are completely random numbers that are seemingly unrelated. so in order to figure out the previous version's contentid I need to use the "prevver" column. That's the point of step 3. — user1068636, Jan 01 '14 at 20:37
You only mention one update statement, updating text_val in os_prop. So are you saying, in the 2nd fiddle, that you have those 2 tables and all those recs already in place but that the os_prop.text_val column is blank? That's the end goal, just to populate, os_prop.text_val? — gfrobenius, Jan 01 '14 at 21:29
@gfrobenius - I just updated the link to the fiddles. It seems they were modified since when I made them. There should only be 1 update per Migrated version in os_prop table. So if we find 1000 contentIDs that were migrated then we would update the os_prop table 1000 times. Basically any NULL value in text_val column for "My.Key" should be updated to be equal to the migrated version's "My.Key" value. — user1068636, Jan 02 '14 at 00:45
@gfrobenius - I just updated my post above to be 100 % clear about what the before and after snapshots should look like and where exactly the problems are. Hopefully the step 3 makes more sense to you now. The problem areas shown above are with the previous versions of migrated pages. The only way to fix the problem area is to know what the previous versions are in the first place. — user1068636, Jan 02 '14 at 00:53
Oh geez, yeah, that's much leader thanks. This is super simple, answer coming after I eat. — gfrobenius, Jan 02 '14 at 03:06
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44291/discussion-between-gfrobenius-and-user1068636) — gfrobenius, Jan 02 '14 at 03:40

score 1 · Accepted Answer · edited May 23 '17 at 10:32

I just free typed this cause I'm away from my work machine right now and I don't quite understand what you are saying for step 3, but this should be plenty to get you on your way. Hope this helps.

--UPDATE-- Thanks for making your question more clear. This will get you want you need. I'm pretty sure it could be re-written in a single update statement (see here) but I chose to do it like this...

declare

begin

--only want to loop the recs we need to fix
for c1 in ( select p.entity_id, 'value'||c.contentid newtext_val from os_prop p , content c
            where p.entity_id = c.prevver
            and p.text_val is null)
loop
    update os_prop set
        text_val = c1.newtext_val
    where entity_id = c1.entity_id
    and text_val is null;
end loop;

commit;

end;
/

are the "???" actual Pl/SQL syntax or are you saying you don't know what goes there? — user1068636, Jan 01 '14 at 20:40

How do you write a pl sql script that iterates finds a previous version of all content objects and updates a property value on each?

1 Answers1