Created
August 23, 2012 13:50
-
-
Save davidpelfree/3436805 to your computer and use it in GitHub Desktop.
How to "upsert" using MySQL: update value, and if it doesn't exist, create it
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| insert into counter_hourly(DataCenter,Env,HostName,CustomerID,Hour,TotalRequests,TotalLatency,TotalBytes) | |
| values | |
| ('DC1','ENV2','NTC-CLFSTGAPP3','Lic_ID1','2012-8-23 10:00','11','2190','8822') | |
| on duplicate key update | |
| `TotalRequests` = `TotalRequests` + '11' , | |
| `TotalLatency` = `TotalLatency` + '2190' , | |
| `TotalBytes` = `TotalBytes` + '8822' | |
| ; | |
| -- Here's the DDL used to create this table. | |
| -- Note that a unique index must be set on the fields defining the key in order to correctly use "on duplicate key": | |
| CREATE TABLE counter_hourly ( | |
| -- Key: | |
| DataCenter varchar(50), | |
| Env varchar(50), | |
| HostName varchar(50), | |
| CustomerID varchar(100), | |
| Hour datetime, | |
| -- Values: | |
| TotalRequests int, | |
| TotalLatency bigint, | |
| TotalBytes bigint | |
| ) | |
| engine=innodb; | |
| -- ----------------- | |
| create unique index idx_counter_hourly on counter_hourly(DataCenter,Env,HostName,CustomerID,Hour); | |
| -- ---------------------------------------------------------- |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment