Only insert not existing rows-CodePudding

I have a table price with columns id,item_name,date,price. There can be multiple prices for an item_name, for example:

ID          item_name          date          price
320           item1         01-04-2022       1.40
321           item1         02-04-2022       1.3
322           item1         03-04-2022       1.42
323           item2         03-04-2022       2.54
324           item2         04-04-2022       2.6

This data comes from an API and my ID is autoincrement. I now have a script which runs multiple times and always fetches this API. Problem is, that the API returns the same data every time, so If I run a normal Insert every time the script executes, I would have duplicate entries like this (note ID):

ID          item_name          date          price
325           item1         01-04-2022       1.40
326           item1         02-04-2022       1.3
327           item1         03-04-2022       1.42
328           item2         03-04-2022       2.54
329           item2         04-04-2022       2.6

What would be the proper SQL-Way of inserting only rows that don't have such date and item_name?

CodePudding user response：

One option would be to use exists logic to block duplicate inserts from happening:

INSERT INTO price (item_name, date, price)
SELECT  -- your data here
WHERE NOT EXISTS (
    SELECT 1
    FROM price
    WHERE date = <date to be inserted> AND
          item_name = <item to be inserted>
);

Another option might be to put a unique index on (date, price). This would then result in an exception upon inserting a duplicate. This option might make the most sense when you don't expect such duplicates to happen very often.