SQL Inserting Multiple Records / Updates Existing (In Single Query)

Question

I'm trying to insert multiple data in a single query.

INSERT INTO tableName (`ITEM`, `QUANTITY`)
VALUES
    ('PAPER',   '1'),
    ('PENCIL',  '1'),
    ('FOLDER',  '1'),
    ('PEN',     '1'),
    ('ERASER',  '1') 

This works. Basically all it does is for every entry (or item such as 'PAPER', 'PENCIL', etc as seen above), it just inserts it as one record.

My issue now is what if the record is already existing? I dont want to insert a new record which has the same details as the already existing one.

I'm trying to figure out how and came across ON DUPLICATE KEY UPDATE.

Here is my new query:

INSERT INTO tableName (`ITEM`, `QUANTITY`)
VALUES
    ('PAPER',   '1'),
    ('PENCIL',  '1'),
    ('FOLDER',  '1'),
    ('PEN',     '1'),
    ('ERASER',  '1') 
ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY=QUANTITY;

Although this works well with inserting, my issue is that all existing records will have a fix value of what it should be updated, not being able to determine which record should have the specified value as a new data.

Needless to say, with this approach, I am unable to determine what value should be inserted as an update to a certain record, as it does the update for all existing data.

I was thinking of an approach something like:

INSERT INTO tableName (`ITEM`, `QUANTITY`)
VALUES
    ('PAPER',   '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='2',
    ('PENCIL',  '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='33',
    ('FOLDER',  '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='19',
    ('PEN',     '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='28',
    ('ERASER',  '1') ON DUPLICATE KEY UPDATE ITEM=ITEM, QUANTITY='14';

Something like this that would help me insert whatever number of records into the table, and if a certain record that I'm trying to insert already exists, will update with a new data instead. But of course this one doesn't work.

I'm trying to find a way how to do something like this, as inserting multiple records in a single query is a requirement.

Note: I am working this on MySQL. Also, I'm trying to avoid using Stored Procedures.

Answer 1

You have a strange situation, where the value you want to update is not being passed in.

Assuming that ITEM is unique:

INSERT INTO tableName (`ITEM`, `QUANTITY`)
    VALUES ('PAPER', 1),
           ('PENCIL', 1),
           ('FOLDER', 1),
           ('PEN', 1),
           ('ERASER', 1)
    ON DUPLICATE KEY UPDATE
       QUANTITY = (CASE ITEM
                       WHEN 'PAPER' THEN 2
                       WHEN 'PENCIL' THEN 33
                       WHEN 'FOLDER' THEN 19
                       WHEN 'PEN' THEN 28
                       WHEN 'ERASER' THEN 14
                   END);

This also assumes that QUANTITY is a number. Single quotes are not needed around numeric constants.

Answer 2

You can use "Merge" statement in SQL server, Please check the below link: https://www.red-gate.com/simple-talk/sql/learn-sql-server/the-merge-statement-in-sql-server-2008/