Skip to Main Content
  • Questions
  • Update Table 2 based on data from Table 1 with Conditions

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, David.

Asked: July 13, 2020 - 8:53 pm UTC

Last updated: July 24, 2020 - 4:28 am UTC

Version: 12g

Viewed 1000+ times

You Asked

I have two tables (target & source) which have the same structure. Target has a subset of accounts & telephone info from the source table. Source has duplicate account # with different contact information in each row.

I’m attempting to update the target table contact information so that a single row / account number has all contact information; additional contact info goes into extended columns on the target table. Keying on the account #, I would like to compare source telephone information to see if it exists in the target table, if it doesn’t exist, add the information in the extended columns as in the example below

REF Account No Cell Site Contact
SRC 3000030 1112374016
SRC 3000030 2226883696
SRC 3000030 3334023239
SRC 3000030 4442548632
TGT 3000030 1112374016

Target Table
Account No Cell Site Contact Phone5 Phone6 Phone7 Phone8
3000030 1112374016 All non duplicate contact numbers go into these over flow columns

Through some research & help from StackOverflow, I crafted a View with a UNION ALL, and a Merge Statement with a Pivot table:

CREATE or REPLACE VIEW col_combine_v  (  accountno, cell, site, contact, email) AS   select  accountno, cell, site, contact, email1 from target union all
   select accountno, cell, site, contact, email1 from source order by accountno desc;

MERGE INTO target tgt
    USING (select  accountno,  r1_cell, r1_site, r1_contact, r1_email, r2_cell, r2_site, r2_contact, r2_email
            from (select s.*,row_number()over(partition by accountno order by accountno) rn
                  from col_combine_v s
                 )
            pivot (
              min(cell) cell,min(site) site, min(contact) contact, min(email) email
               FOR rn
              IN (1 R1,2 R2)
           )
          ) src 
    ON (src.accountno = tgt.accountno)
WHEN MATCHED
    THEN 
        UPDATE SET
                  tgt.phone5 =
                  CASE
                    WHEN src.r1_contact <> src.r2_contact
                        AND src.accountno = tgt.accountno
                        THEN src.r1_contact
                    END,
                    tgt.phone6 =
                    CASE
                        WHEN src.r1_contact <> src.r2_contact
                        AND src.r1_contact <> tgt.phone6
                        AND src.accountno = tgt.accountno
                        THEN src.r1_contact
                    END


Unfortunately, after I perform the PIVOT, I don’t see any of the other contact numbers except for the next to the last one (4442548632). This is the only number that gets updated on the Target table. But I need to extract all the telephone numbers that don’t exist in the Target table.

I’m relatively new to Oracle / SQL and appreciate your help and guidance.

7/14/20: I have updated the LiveSQL link previously provided in a attempt to mimic as much as possible the use case I'm trying to solve.

I created a view to return 3 rows of data(1). If I select the data from the view, it returns 3 rows of data, (3). Once I create a Pivot table from the view, only two rows are return, (2). I need access to all the data in the view so I can update the target table accordingly.
Basically I'm trying to determine if the Source "Contact" information exists in the Target table and if it doesn't exist, put the Source data in extended columns, i.e. phone4, phone5, etc. Note that the Target columns cell, site, contact are duplicates of the Source table equivalent columns.
It's my understanding that I can't compare two tables independently because I have no assurances that the row numbers are the same.
Essentially how does one compare two tables (A & B) so that I can use data in Table A to update the data in Table B based on some conditions.

Thank you


with LiveSQL Test Case:

and Connor said...

I'm still not entirely sure of your requirement (thats me not you :-)) but hopefully this will assist.

My logic is:

- transpose existing targets to rows
- combine with source to find existing matches
- pivot back to a row per account
- use that as input to merge with the target


SQL>
SQL> Create TABLE source (accountno integer, cell number, site number, contact number);

Table created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);

1 row created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,2226883696);

1 row created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,3334023239);

1 row created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000030,1 ,2 ,4442548632);

1 row created.

SQL>
SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,1112374016);

1 row created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,2226883696);

1 row created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,3334023239);

1 row created.

SQL> Insert INTO source (accountno, cell, site, contact) Values (3000031,1 ,2 ,4442548632);

1 row created.

SQL>
SQL> Create TABLE target (accountno integer, cell number, site number,
  2                      contact number, phone4 number, phone5 number, phone6 number);

Table created.

SQL> Insert INTO target (accountno, cell, site, contact) Values (3000030,1 ,2 ,1112374016);

1 row created.

SQL>
SQL> with target_rows as
  2  (
  3  select   ACCOUNTNO      , CELL ,      SITE ,   1 PHONE_IDX,      contact PHONE
  4  from target
  5  union all
  6  select ACCOUNTNO      , CELL ,      SITE ,   PHONE_IDX,      PHONE from target
  7  unpivot ( phone for phone_idx in ( phone4 as 2, phone5 as 3,phone6 as 4) )
  8  ),
  9  candidates as
 10  (
 11  select s.ACCOUNTNO, s.CELL, s.SITE, nvl(t.phone, s.CONTACT) contact,
 12           max(t.phone) over ( partition by s.accountno) tag,
 13           row_number() over ( partition by s.accountno order by t.phone ) as seq
 14  from source s, target_rows t
 15  where s.accountno=t.accountno(+)
 16  and s.cell=t.cell(+)
 17  and s.site=t.site(+)
 18  and s.contact=t.phone(+)
 19  ),
 20  transposed as
 21  (
 22    select *
 23    from   candidates
 24    pivot
 25    ( min(contact) for seq in (1 as P1, 2 as P2, 3 as P3, 4 as P4)
 26    )
 27  )
 28  select * from transposed;

 ACCOUNTNO       CELL       SITE        TAG         P1         P2         P3         P4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
   3000030          1          2 1112374016 1112374016 3334023239 2226883696 4442548632
   3000031          1          2            1112374016 2226883696 3334023239 4442548632

SQL>
SQL>


So that final result is now a potential source for your merge command.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library