Обсуждение: What is the best way to merge two disjoint tables?

Hi Gurus,

I have two disjoint tables, for examples, of user lists.

Some users appear both tables. They may or may not have the same u_id.
Some users only appear one table.

I would like to merge tableB into tableA with the condition that each
user has a unique id.
Also I would like to keep u_id in the tableB. This requires to modify
u_id of tableA if there are matching users but different u_id.
Also those users only in tableA should change their u_id, if needed, to
avoid any conflict with u_id from tableB.

Can someone show me an example SQL statement?

tableA:
  u_user  | u_id
----------+------
 user1   |   46
 user2   |   30
 user4   |   22
 user5   |   48
 user7   |    6
user9    |    1
user11  |   45
user12  |   33

tableB:
  u_user  | u_id
----------+------
user1    |   46
user2    |   30
user3   |   22
user6   |   48
user8   |    6
user10  |    1

Thanks,

- Chansup

On 9/7/07, Chansup Byun <Chansup.Byun@sun.com> wrote:
> Can someone show me an example SQL statement?

I suppose you could add a constant, non-overlapping number to add to
the duplicate IDs, say 1000, and then this:

SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
     , COALESCE(CASE
                  WHEN EXISTS(SELECT 1
                                FROM TABLEB
                               WHERE U_ID = T1.U_ID
                                 AND U_USER <> T1.U_USER)
                    THEN T1.U_ID + 1000
                  ELSE T1.U_ID
                END
              , T2.U_ID
               ) AS U_ID
  FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER

will generate a new list of U_USERs and U_IDs.

Good luck.

Rodrigo De León wrote:
> On 9/7/07, Chansup Byun <Chansup.Byun@sun.com> wrote:
>
>> Can someone show me an example SQL statement?
>>
>
> I suppose you could add a constant, non-overlapping number to add to
> the duplicate IDs, say 1000, and then this:
>
> SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
>      , COALESCE(CASE
>                   WHEN EXISTS(SELECT 1
>                                 FROM TABLEB
>                                WHERE U_ID = T1.U_ID
>                                  AND U_USER <> T1.U_USER)
>                     THEN T1.U_ID + 1000
>                   ELSE T1.U_ID
>                 END
>               , T2.U_ID
>                ) AS U_ID
>   FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER
>
> will generate a new list of U_USERs and U_IDs.
>
> Good luck.
>
This is cool!
What I wanted is shown below.

One more question: Is there a way to make the  T2.U_ID + 1000  number to be incremental from a given number instead of
adding1000? 


SELECT COALESCE(T1.U_USER, T2.U_USER) AS U_USER
     , COALESCE(CASE
                  WHEN EXISTS(SELECT 1
                                FROM TABLEB
                               WHERE U_ID = T1.U_ID
                                 AND U_USER <> T1.U_USER)
                    THEN T1.U_ID
                  ELSE T1.U_ID
                END
              , T2.U_ID + 1000
               ) AS U_ID
  FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER



thanks,

- Chansup

On 9/7/07, Chansup Byun <Chansup.Byun@sun.com> wrote:
> One more question: Is there a way to make the  T2.U_ID + 1000  number to be incremental from a given number instead
ofadding 1000? 

See here:
http://archives.postgresql.org/pgsql-sql/2007-05/msg00194.php

Then, say we want to start from 49:

SELECT   COALESCE(T1.U_USER, T2.U_USER) AS U_USER
       , COALESCE(T1.U_ID
                , CASE
                    WHEN T2.U_ID IS NOT NULL
                      THEN 48 + ROWNUM()
                  END
                 ) AS U_ID
    FROM TABLEA T1 FULL JOIN TABLEB T2 ON T1.U_USER = T2.U_USER
ORDER BY U_ID