MySQL update if two values already exist, else insert

Question

I've been trying to figure out how to get my query to update an existing row if 2 values match

I have a table with this data

id                                   | itemid | date                | price
____________________________________________________________________________
eef1879a-4506-437c-801a-b874e38e290d | 123    | 2015-04-26 08:42:32 | 3.42
67391c5e-09ab-4c2f-b80e-fb0ce69f6e5d | 123    | 2015-04-27 20:02:32 | 3.50
6b16fba4-389e-40ae-94f8-7917ab09fd39 | 13512  | 2015-04-26 08:13:32 | 1.54
5ec3dfe0-29bf-48c8-a694-89606cdbfba3 | 13512  | 2015-04-27 20:02:32 | 1.70
808dc4a3-daa0-4470-b08a-4650f7f4d8e9 | 2124   | 2015-04-26 08:42:28 | 8.74
e327aa9e-fe02-4ccb-8543-752fe5d86e2c | 2124   | 2015-04-27 20:02:32 | 9.04
de4d69ce-eca0-419f-8514-1cc0509149dd | 2124   | 2015-04-28 17:04:02 | 9.78
f7efdcf3-9dd1-41ee-880b-b18563d6f934 | 13512  | 2015-04-28 13:07:30 | 2.09
c256fed7-8a09-4afe-97f3-0e5a9ceea930 | 123    | 2015-04-28 02:08:38 | 3.52

I have an insert query that's working fine. But I don't want multiple entries per day. I've seen ON DUPLICATE KEY for a single column unique key, but my PK is a uuid v4 that's generated via PHP on the insert.

I'm currently checking in a SQL query if the value exists, if it doesn't to insert it. However this is creating an issue if the process gets kicked off more than once. I'm trying to failsafe not having duplicate prices per day.

Current SQL to check if exists:

$date = DATE('Y-m-d');

SELECT i.id FROM items as i
LEFT JOIN itemprices as ip
ON i.id=ip.itemid
AND date(ip.date) = \"$date\"
WHERE ip.itemid IS NULL

It checks the list of item's to see what it needs to create a price for that day. The array that comes back from this is valid at this point.

Then I just do an insert per item with the appropriate value that I get from my endpoint.

Currently my data set that I'm getting prices for each day is 14000 lines, so processing things more than once is extra stress on MySQL and requires manual cleanup.

you can check with SELECT COUNT if the value exists and then decide if the query returns a value > 0. But you want to do it in a single query, right? — user2399035
– user2399035, Commented Apr 30, 2015 at 17:17
ON DUPLICATE KEY also works if you put a unique restraint on the combination of multiple columns. You would need to have a date-only column though, as the times will make it not-unique. — jeroen
– jeroen, Commented Apr 30, 2015 at 17:19
@LelioFaieta I'm doing that in separate queries right now, and that's what's causing the problem. So regardless I would have to modify the existing data? I have 100k rows of existing data that I was attempting to avoid modifying. — Andrew
– Andrew, Commented Apr 30, 2015 at 17:23
You could split the date and the time in 2 fields to avoid storing duplicate data. I don't think the database would have a problem with that, but you would possibly have to modify your insert query. On the other hand the time does not seem relevant for you so you might as well get rid of it altogether. — jeroen
– jeroen, Commented Apr 30, 2015 at 17:26
Do you have any example of combinations of multiple columns if I did split those out? Splitting the DATETIME to a DATE and a TIME would be pretty simple. — Andrew
– Andrew, Commented Apr 30, 2015 at 17:29

John Cartwright · Accepted Answer · 2015-04-30 17:22:17Z

2

... ON DUPLICATE KEY ..

Also applies for composite unique/primary keys. Simply rebuild your PK as a composite index.

answered Apr 30, 2015 at 17:22

John Cartwright

5,07424 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Over a year ago

Thank you, this is a great description

Collectives™ on Stack Overflow

MySQL update if two values already exist, else insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related