2

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
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Mark Twain
  • 199
  • 1
  • 2
  • 10

1 Answers1

1

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.

e4c5
  • 52,766
  • 11
  • 101
  • 134
  • While fetching, I need to `SELECT` `occ_date` where `counter` > 5 `AND` `service_tag` = `service-1`. That's why I need to increment the `counter` for same date and same service, i.e. only when `occ_date` and `service_tag` both already exists in a single row, else `INSERT` new row. – Mark Twain Jan 31 '17 at 06:51
  • Thanks, it worked as expected, I didn't know I could it in this way as well. Thanks for your patience and effort!! May your business grows quickly!! – Mark Twain Jan 31 '17 at 08:55
  • 1
    Glad to have helped. Thank you for your kind comments. All the best with your project – e4c5 Jan 31 '17 at 08:57