2

I have a table people with less than 100,000 records and I have taken a backup of this table using the following:

create table people_backup as select * from people

I add some new records to my people table over time, but eventually I want to merge the records from my backup table into people. Unfortunately I cannot simply DROP my table as my new records will be lost!

So I want to update the records in my people table using the records from people_backup, based on their primary key id and I have found 2 ways to do this:

  1. MERGE the tables together
  2. use some sort of fancy correlated update

Great! However, both of these methods use SET and make me specify what columns I want to update. Unfortunately I am lazy and the structure of people may change over time and while my CTAS statement doesn't need to be updated, my update/merge script will need changes, which feels like unnecessary work for me.

Is there a way merge entire rows without having to specify columns? I see here that not specifying columns during an INSERT will direct SQL to insert values by order, can the same methodology be applied here, is this safe?

NB: The structure of the table will not change between backups

Community
  • 1
  • 1
Rossiar
  • 2,416
  • 2
  • 23
  • 32
  • You always need to specify which columns will be updated. That's just how it works. You could, however, write a script to write a script - eg. `select 'set tgt.'||column_name||' = src.'||colunn_name||',' from user_tab_columns where table_name = 'TABLE_NAME';` that would at least be able to generate the list of columns for you, so you can copy and paste into your statement – Boneist Nov 04 '15 at 17:13
  • 2
    Given that your table is small, could you simply `delete` anything from the table that exists in the backup and then do an `insert` of everything in the backup? That's slow and not particularly elegant but it meets your requirements. – Justin Cave Nov 04 '15 at 17:14
  • Do you want to replace new data with old? – Dan Bracuk Nov 04 '15 at 17:36
  • @JustinCave if you could post as answer I'll accept – Rossiar Nov 06 '15 at 10:33

1 Answers1

2

Given that your table is small, you could simply

DELETE FROM table t
 WHERE EXISTS( SELECT 1
                 FROM backup b
                WHERE t.key = b.key );

INSERT INTO table
  SELECT *
    FROM backup;

That is slow and not particularly elegant (particularly if most of the data from the backup hasn't changed) but assuming the columns in the two tables match, it does allow you to not list out the columns. Personally, I'd much prefer writing out the column names (presumably those don't change all that often) so that I could do an update.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384