CODEWITHSUNDEEP
mysqlprimary-keymultiple-columnsunique-indexon-duplicate-key
Mark Twain
Mark Twain

Reputation: 227

MySQL — ON DUPLICATE KEY UPDATE only when both unique fields match, else INSERT

I have the following query: 

"INSERT INTO `occ_apps` (`occ_date`, `service_tag`, `counter`) VALUES (?, ?, '1') ON DUPLICATE KEY UPDATE `counter` = (`counter`+1)"

Currently it's incrementing the counter when either occ_date or service_tag is matching in a row. Where occ_date and service_tag are unique fields, and I can't set primary key to both unique fields.

I ran the following: 

ALTER TABLE occ_apps
DROP PRIMARY KEY,
ADD PRIMARY KEY (occ_date, service_tag);

And I get, the error: 

`#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key`

I want it to update (increment) the counter only when occ_date and service_tag both matches (already exists) in a single row, otherwise it should insert a new row.

Software version: 5.5.53-MariaDB-1~wheezy - mariadb.org binary distribution

when I ran DESC occ_apps I get:

Field           Type            Null        Key     Default     Extra

serial_no       int(255)        NO          PRI     NULL          auto_increment
occ_date        varchar(255)    NO          UNI     NULL        
counter         int(255)        NO                  NULL    
service_tag     varchar(255)    YES         UNI     NULL

Upvotes: 2

Views: 720

Answers (1)

e4c5
e4c5

Reputation: 53734

I don't think you even need a counter field in your table. It looks like your counter is merely holding how many times a given value occurs. And that's something that can be generated easily using a GROUP BY

SELECT occ_date, COUNT(*) FROM occ_apps GROUP BY `occ_date`;

So you want to filter the query so that you get only items with at least 5 counts?

SELECT occ_date, COUNT(*) FROM occ_apps  WHERE service_tag = 'service-1'
GROUP BY `occ_date` HAVING COUNT(*) > 5

These sorts of problems have been solved millions of times using GROUP BY. This is just the tip of the ice berge as far as what SQL query aggregation can do. Please take a moment to read up on it.

Upvotes: 1

Related Questions