I am trying to append data from two tables in my dataBase to a third table with this code in a Microsoft Access database:
INSERT INTO percentage ( productId, productName, salesPrice, currentProvider, offerProvider, offerPrice, percentage )
SELECT products.productId AS productId, products.productName AS productName, products.pricePerUnitOrKg AS salesPrice, products.providerId AS currentProvider, productsByProviders.providerId AS offerProvider, productsByProviders.pricePerUnitOrKg AS offerPrice, Round(products.pricePerUnitOrKg/productsByProviders.pricePerUnitOrKg,3) AS percentage
FROM products INNER JOIN productsByProviders ON products.productId = productsByProviders.productId
Since the primary key in percentage is a composite key combined of the two fields: productId and offer provider, I want to make sure the row, (offer) - doesn't already exist in the percentage table with this code:
WHERE NOT Exists (SELECT 1 FROM percentage WHERE percentage.productId = productsByProvider.productId AND percentage.offerProvider = productsByProvider.providerId));
which I saw used here: How to use NOT EXISTS with COMPOSITE KEYS in SQL for inserting data from POJO
This isn't working, and I keep getting an error when trying to append to the table, which says:
And when I click yes, it doesn't append the rows that don't exist in the percentage table, which is what I want it to do.
Why is this happening?
I removed the NOT EXISTS part of the query, like June7 suggested in the comments, and it works, the error is still showing up, but for my purposes, this is enough.