CODEWITHSUNDEEP
phpmysql
Andrew
Andrew

Reputation: 4441

MySQL update if two values already exist, else insert

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.

Upvotes: 0

Views: 441

Answers (1)

John Cartwright
John Cartwright

Reputation: 5084

... ON DUPLICATE KEY ..

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

Upvotes: 2

Related Questions