Primary key violation while merging data from another table

Question

I have two tables, TBTC03 and TBTC03Y, with TBTC03Y having two extra columns as EFFDTE and EXPDTE. I have to merge the data from TBTC03 to TBTC03Y with the following logic:

If no matching TC03 entry is found in TC03Y
a new TC03Y record is build with the TC03 data
the Effective Date will default to '01-01-1980'
the Expiration Date will default to '09-30-1995'

I wrote a query for the same as :

insert into  TBTC03Y (LOB,MAJPERIL,LOSSCAUSE,NUMERICCL,EFFDTE,EXPDTE)
    select LOB,MAJPERIL,LOSSCAUSE,NUMERICCL,'0800101' ,'0950930'
     from TBTC03 where not EXISTS (select * from TBTC03Y where 
TBTC03Y.LOB =        TBTC03.LOB AND 
TBTC03Y.MAJPERIL   = TBTC03.MAJPERIL   AND
TBTC03Y.LOSSCAUSE  = TBTC03.LOSSCAUSE  AND
TBTC03Y.NUMERICCL  = TBTC03.NUMERICCL)

The primary key for both the tables is LOB, MAJPERIL and LOSSCAUSE.

However i have some TBTC03Y records, that already have the data with the primary key.

Firing the above query gives primary key constraints on some of the rows. I am unable to figure out how i can acomplish it.

Sample data and desired results would really help explain what you want to do. — Gordon Linoff, Apr 19 '16 at 11:12
Thanks Gordon, while writing the sample data I figured out the problem. :D — CHS, Apr 19 '16 at 11:27

score 3 · Answer 1 · answered Apr 19 '16 at 11:17

If the PK for both tables is {LOB, MAJPERIL, LOSSCAUSE}, you should remove TBTC03Y.NUMERICCL = TBTC03.NUMERICCL from your where clause.

Example:

t1{LOB, MAJPERIL, LOSSCAUSE, NUMERICCL}
   1    1         1          1

t2{LOB, MAJPERIL, LOSSCAUSE, NUMERICCL}
   1    1         1          2

In t2 there is no row where:

TBTC03Y.LOB =        TBTC03.LOB AND 
TBTC03Y.MAJPERIL   = TBTC03.MAJPERIL   AND
TBTC03Y.LOSSCAUSE  = TBTC03.LOSSCAUSE  AND
TBTC03Y.NUMERICCL  = TBTC03.NUMERICCL

But inserting will obvioulsy violate PK constraint in t2:

t2{LOB, MAJPERIL, LOSSCAUSE}
   1    1         1

score 3 · Accepted Answer · answered Apr 19 '16 at 11:19

3

The issue with the primary key is that you're also including NUMERICCL in the WHERE clause. If you remove this you'll then be inserting unique data.

You may have to create a separate process as it appears you have some records in each table that have the same LOB, MAJPERIL and LOSSCAUSE but have a different NUMERICCL. I can think of three options here;

You have an issue with the data that needs fixing.
Maybe you want to update this value to match, in which case you're looking at an UPDATE rather than INSERT INTO.
You need to update your composite primary key to include the column NUMERICCL.
Removing NUMERICCL from the where clause would also correct this.

answered Apr 19 '16 at 11:19

Rich Benner

7,873
9
33
39

1

Ohh yes. i figured it out while trying to write the sample data for the question! Thank you!! – CHS Apr 19 '16 at 11:22
Excellent, asking a question is a great way of working something out for yourself :). I sit next to a project manager who is in no way techie and sometimes ask her questions that I'm stuck on. She can't help for the most part but the point is that asking the question often triggers something in my head. – Rich Benner Apr 19 '16 at 11:28

Primary key violation while merging data from another table

2 Answers2