0

I have 2 tables : A and B with same columns on both the table as :

Customer_Part_Number, Lear_Part_Number, Shipping_ID, Customer_Name, Effective_Date, End_Date,Change_ID, PO_Number, PO_Price 

I have successfully copied all the data to table B from table A But my scenario when the records of the columns :

TENANT_ID, CUSTOMER_PART_NUMBER, SHIPPING_ID, EFFECTIVE_DATE 

are same in table A,I don't want it to get it inserted to table B. I have tried using the query :

INSERT INTO OSUSR_1SV_QAD_PO_DATA (TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE) SELECT TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE FROM OSUSR_1SV_STAGING_FTP A WHERE not exists ( SELECT TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE FROM OSUSR_1SV_QAD_PO_DATA B WHERE A.TENANT_ID = B.TENANT_ID and A.CUSTOMER_PART_NUMBER = A.CUSTOMER_PART_NUMBER and A.SHIPPING_ID = B.SHIPPING_ID and A.EFFECTIVE_DATE = B.EFFECTIVE_DATE ) 

Here, If the record of the columns TENANT_ID,CUSTOMER_PART_NUMBER,SHIPPING_ID,EFFECTIVE_DATE will be same, it wont be inserting the duplicate records.

Kindly help me out.

enter image description here

In the first Image there are duplicate data on table OSUSR_1SV_STAGING_FTP the basis of
TENANT_ID,
CUSTOMER_PART_NUMBER,
SHIPPING_ID,
EFFECTIVE_DATE

But I need to insert the data like Image 2 on table OSUSR_1SV_QAD_PO_DATA without duplication of TENANT_ID,
CUSTOMER_PART_NUMBER,
SHIPPING_ID,
EFFECTIVE_DATE
enter image description here

I have tried this query
MERGE OSUSR_1SV_QAD_PO_DATA A
USING OSUSR_1SV_STAGING_FTP B
ON (B.TENANT_ID = A.TENANT_ID and B.CUSTOMER_PART_NUMBER = A.CUSTOMER_PART_NUMBER
and B.SHIPPING_ID = A.SHIPPING_ID and B.EFFECTIVE_DATE = A.EFFECTIVE_DATE )
WHEN MATCHED THEN
DELETE WHERE ID NOT IN (SELECT MIN(ID)_
FROM OSUSR_1SV_STAGING_FTP
GROUP BY CUSTOMER_PART_NUMBER,SHIPPING_ID)
WHEN NOT MATCHED THEN
INSERT (TENANT_ID,CUSTOMER_PART_NUMBER,LEAR_PART_NUMBER,SHIPPING_ID,CUSTOMER_NAME,PROGRAM_NAME,EFFECTIVE_DATE,END_DATE,CHANGE_ID,PO_NUMBER,PO_PRICE)
VALUES(B.TENANT_ID,B.CUSTOMER_PART_NUMBER,B.LEAR_PART_NUMBER,B.SHIPPING_ID,
B.CUSTOMER_NAME,B.PROGRAM_NAME,B.EFFECTIVE_DATE,B.END_DATE,B.CHANGE_ID,B.PO_NUMBER,B.PO_PRICE);

1
  • Why doesn't your query work? Commented Mar 29, 2016 at 12:21

2 Answers 2

1

Replace this part

not exists ( SELECT 1 FROM OSUSR_1SV_QAD_PO_DATA B WHERE A.TENANT_ID = B.TENANT_ID --and A.CUSTOMER_PART_NUMBER = A.CUSTOMER_PART_NUMBER --you are Comparing same value here and A.CUSTOMER_PART_NUMBER = B.CUSTOMER_PART_NUMBER and A.SHIPPING_ID = B.SHIPPING_ID and CAST(A.EFFECTIVE_DATE AS DATE) = CAST(B.EFFECTIVE_DATE AS DATE) ) 
Sign up to request clarification or add additional context in comments.

5 Comments

ITS NOT WORKING.. I AM GETTING AL THE RECORDS INCLUDING THE DUPLICATE RECORDS.
KINDLY SEE THE EDIT PART I HAVE DONE.. aTTACHED THE IMAGE TOO ITS NOT WORKING TOO
@Rains have you tried exact same as I demonstrated??.
@Rains I observe that duplicate records are not inserted, observe the effective date of each record is different. Still if you dont want those record then try updated my Ans
Thank you so much... just did slight changes in the query. Its working fine.
0

Try this Query :

 INSERT INTO OSUSR_1SV_QAD_PO_DATA (TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE) Select TENANT_ID, CUSTOMER_PART_NUMBER, LEAR_PART_NUMBER, SHIPPING_ID, CUSTOMER_NAME, PROGRAM_NAME, EFFECTIVE_DATE, END_DATE, CHANGE_ID, PO_NUMBER, PO_PRICE from OSUSR_1SV_STAGING_FTP A INNER JOIN (select TENANT_ID,CUSTOMER_PART_NUMBER,SHIPPING_ID,EFFECTIVE_DATE from OSUSR_1SV_STAGING_FTP group by TENANT_ID,CUSTOMER_PART_NUMBER,SHIPPING_ID,EFFECTIVE_DATE) as NoDuplicate ON A.TENANT_ID=NoDuplicate.TENANT_ID 

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.