1

I have a master table as:

id(PRIMARY KEY) customername    zipcode identitynumber  createdtimestamp
ABC                 JOHN         91421     12346          2:09:31 AM
DEF                 CARTER       91421     12346    2:09:31 AM
PQR                AURTHUR       13133     767676   2:09:31 AM

and another table child as below. 

ID(PRIMARY) customername    zipcode identitynumber  
xyz           peter          91421  12346   
MNO           CARTER         91421  12346

I want to get all the records from master table that are having createdtimestamp is less than 24 hours and insert in to child table only if zipcode, identitynumber match but not customername. Basically inserting various names in child table having same zipcode and identitynumber and primary key of master table. So, finally child table should has:

ID(PRIMARY) customername    zipcode identitynumber  
xyz          peter            91421 12346   
MNO          CARTER           91421 12346   
ABC          JOHN             91421 12346

I tried with below query but failing as primary key violation even though child tables doesn't have primary key.

insert into childtable
    (ID,customername,zipcode,identitynumber)
    select
    master.id ,
    master.customername,
    child.zipcode,
    child.identitynumber
    FROM   childtable  child
    inner JOIN mastertable master ON master.CREATED_TIMESTAMP > DATE_SUB(NOW(), INTERVAL 24 HOUR)
    and master.identitynumber = child.identitynumber 
    AND master.zipcode = child.zipcode
    WHERE  NOT EXISTS 
    ( SELECT 1  FROM childtable existingchild
      WHERE  existingchild.identitynumber = master.identitynumber
      AND existingchild.customername  = master.customername
      AND existingchild.zipcode  =  master.zipcode);

Please advise with best query.

John
  • 43
  • 6
  • `NOT EXITS` should be `NOT EXISTS`. – Barmar Aug 10 '18 at 03:04
  • **even though child tables doesn't have primary key.** Your diagram shows that it has Primary Key = `ID`. – Barmar Aug 10 '18 at 03:06
  • Thanks for comment!.child tables has xyz, MNO as primary key, but for output ABC is primary key from master table – John Aug 10 '18 at 03:09
  • Try doing just the `SELECT` and see what it returns, then compare that to the existing contents of `child`. – Barmar Aug 10 '18 at 03:11
  • What is the exact error message? – Barmar Aug 10 '18 at 03:12
  • Error Code: 1062. Duplicate entry 'ABC' for key 'PRIMARY'. even though ABC is not in child table – John Aug 10 '18 at 03:21
  • Does the `SELECT` return multiple rows with ID = ABC? The duplicate occurs when it tries to insert the second one. – Barmar Aug 10 '18 at 14:47
  • Yes, SELECT is returning multiple rows. I am testing with distinct. – John Aug 10 '18 at 23:06
  • If the rows have different `zipcode` or `identitynumber`, DISTINCT won't fix it. – Barmar Aug 10 '18 at 23:29
  • Thanks for your reply! Yes! if it same then working file else failing again – John Aug 10 '18 at 23:35
  • You need to come up with a criteria to select which child row should be returned by the join when there are multiple, and put that into the `SELECT`. You might want to look at https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 – Barmar Aug 10 '18 at 23:40

0 Answers0