Topic
  • 1 reply
  • Latest Post - ‏2014-01-30T13:26:49Z by G.Paulus
Rabaail
Rabaail
7 Posts

Pinned topic How to get MAX from multiple tables

‏2014-01-29T11:35:52Z |
Hi all,
 
I have to perform a data migration and merge three identical databases into one.  I will be using one database as destination and the other two will be merged into that. All databases have around 600 tables.
Now the problem is it cannot have duplicate IDs, primary keys, and columns with auto incremented values.
I was thinking of getting MAX(ID) from all tables of DATABASE 1. Then to do something like
UPDATE TABLE SET ID = ID + 10000 (Or maximum value) for DATABASE 2 AND DATABASE 3.
I have to get the MAX of ALL (600) tables and then do the workaround. Can anyone tell me a way out to get the MAXIMUM of different columns in multiple tables?
  • G.Paulus
    G.Paulus
    10 Posts

    Re: How to get MAX from multiple tables

    ‏2014-01-30T13:26:49Z  

    Hi,

     

    you can query the syscat view SYSCAT.COLIDENTATTRIBUTES with the following select statement to get the next cached first value for the identity columns of all tables.

    SELECT MAX(NEXTCACHEFIRSTVALUE) FROM SYSCAT.COLIDENTATTRIBUTES

    Use this value for update and you are safe from getting duplicates.

    Regards,

    Gerhard Paulus