Adding multiple rows and catching non inserted ones

Question

I have a table with two (relevant) columns : reference, and count. I want to do a query that would add multiple references, and does the following :

• if a reference I'm adding doesn't exist, insert a new row with this reference and its count
• if a reference I'm adding does already exist in the table, don't insert a new row, just increase the count for this reference.

I've thought about doing something like that :

INSERT IGNORE INTO table (reference, count) VALUES ($ref1, $newquantity1), ($ref2, $newquantity2), ($ref3, $newquantity3) //etcaetera 

// Here I'd catch the ignored rows ( = the references that are already in the table)

UPDATE table
SET count = CASE reference 
   WHEN '.$reference_already_present_1.' THEN 'count = count + '.$newquantity1.'
   WHEN '.$reference_already_present_2.' THEN 'count = count + '.$newquantity2.'
    //and so on for every reference already in the table
END
WHERE reference IN($reference_already_present_1, $reference_already_present_2);

(This update query came from here)

The thing is, I don't know if I can catch the ignored rows from the first insertion, and if so, how to do it. Is it possible ?

If not, how else could I achieve what I need ?

(If it's any relevant, I'm using php, pdo and mysql).

Thanks

Answer 1

You want insert . . . on duplicate key update. But first, you need a unique index:

create unique index idx_table_reference on table(reference);

insert into table(reference, count)
    values($reference, $count)
    on duplicate key update count = count + values(count);