I often deal with this by creating a temporary table, recording in the temporary table whether the record is new or not, doing an UPDATE only on the rows that are not new, and doing an INSERT with the new rows. Here's a complete example:
## THE SETUP
# This is the table we're trying to insert into
DROP TABLE IF EXISTS items;
CREATE TABLE items (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) UNIQUE,
price INT
);
# Put a few rows into the table
INSERT INTO items (name, price) VALUES
("Bike", 200),
("Basketball", 10),
("Fishing rod", 25)
;
## THE INSERT/UPDATE
# Create a temporary table to help with the update
DROP TEMPORARY TABLE IF EXISTS itemUpdates;
CREATE TEMPORARY TABLE itemUpdates (
name VARCHAR(100) UNIQUE,
price INT,
isNew BOOLEAN DEFAULT(true)
);
# Change the price of the Bike and Basketball and add a new Tent item
INSERT INTO itemUpdates (name, price) VALUES
("Bike", 150),
("Basketball", 8),
("Tent", 100)
;
# For items that already exist, set isNew false
UPDATE itemUpdates
JOIN items
ON items.name = itemUpdates.name
SET isNew = false;
# UPDATE the already-existing items
UPDATE items
JOIN itemUpdates
ON items.name = itemUpdates.name
SET items.price = itemUpdates.price
WHERE itemUpdates.isNew = false;
# INSERT the new items
INSERT INTO items (name, price)
SELECT name, price
FROM itemUpdates
WHERE itemUpdates.isNew = true;
# Check the results
SELECT * FROM items;
# Results:
# ID | Name | Price
# 1 | Bike | 150
# 2 | Basketball | 8
# 3 | Fishing rod | 25
# 4 | Tent | 100
The INSERT IGNORE INTO approach is simpler, but it ignores any error, which isn't what I want. And I agree that this is strange behavior on the part of MySQL but it's what we've got to work with.
