I have Table_P that has columns Product, region, salesperson, production date, end date. (There are other columns, but those fields don’t matter to me). Product_X has 6 rows: 6 regions, 3 different salespeople and 6 date sets (production date and end date). I want to add a new product, Product_Y to the table, and line by line mirror what Product_X has listed for each column to Product_Y (region, salesperson, production date, end date). I also want to update the existing Product_W, adding all of the same data that I added to Product_Y had without loosing any of the existing unique entries for Product_W.
I tried using this:
INSERT INTO Table_P
(PRODUCT, REGION, SALESPERSON, PROD_DATE, END_DATE)
SELECT 'PRODUCT_W', REGION, SALESPERSON, SYSDATE(), TO_DATE ('31-12-9999', 'DD-MM-YYYY')
FROM Table_P
WHERE PRODUCT = 'PRODUCT_X'
This works, but it also adds records from PRODUCT_X that already match what’s in PRODUCT_W, resulting in duplicates. I need to add a WHERE NOT… or something to check for existing matches and skip those.
This sql statement needs to run in Snowflake – if that helps.
Stephen Adair is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
3
-- Insert data from Product_X to Product_W, avoiding duplicates
INSERT INTO Table_P (PRODUCT, REGION, SALESPERSON, PROD_DATE, END_DATE)
SELECT 'PRODUCT_W', REGION, SALESPERSON, PROD_DATE, END_DATE
FROM Table_P AS px
WHERE PRODUCT = 'PRODUCT_X'
AND NOT EXISTS (
SELECT 1
FROM Table_P AS pw
WHERE pw.PRODUCT = 'PRODUCT_W'
AND pw.REGION = px.REGION
AND pw.SALESPERSON = px.SALESPERSON
AND pw.PROD_DATE = px.PROD_DATE
AND pw.END_DATE = px.END_DATE
);
Inserting data from Product_X to Product_W, avoiding duplicates:
This query checks if a similar row already exists in Product_W before inserting it.
The NOT EXISTS clause ensures that only rows that do not already exist in Product_W are inserted.
It compares the REGION, SALESPERSON, PROD_DATE, and END_DATE columns to ensure no duplicates.
I hope this helps
1