Did You Know #33 – Updating an Entire Row

The background story for that is long and complex, so I won’t get into it. I’ll just say that it was related to replication and I wrote a procedure to fix some issues that we’ve got.

Since this is a generic code for all replicated table, it was VERY generic. I didn’t have the table names, columns or even the owner. Everything had to be dynamic.

One of the problem I ran into was overwriting a row from one table to another. I didn’t want to do “delete” and then “insert” as I had constraints in place. Building such a dynamic update is a pain! You have to build the list of columns to generate the update of all rows.

Then a friend helped me and told me that you can update an entire row in PL/SQL, so here it is (and it’s much easier to build something like that dynamically:

DECLARE
   vr_Rec tab_owner.source_tabname%ROWTYPE;
   src_id tab_owner.source_tabname.id%TYPE;
   tgt_id tab_owner.target_tabname.id%TYPE;
BEGIN
   src_id=<enter id here>;
   tgt_id=<enter id here>;

   SELECT * INTO vr_Rec 
   FROM tab_owner.source_tabname where id = :src_id;

   UPDATE tab_owner.target_tabname set row = vr_Rec where id = tgt_id;
END;
/