Create/Update a row depending on values of other columns in table in MySql -
following table schema, using mysql 5.7 version
create table rule_reports ( pkey int(11) not null auto_increment, email varchar(250) default null, domain varchar(250) not null, rule_id bigint(20) unsigned not null, rule_type varchar(250) not null, log_time datetime default null, count int(11) default null, primary key (pkey), key dwl (domain,rule_id,log_time) )
i want increment count column instead of new row in table, if combination of values of domain,rule_id,rule_type exists in table row
sample rows of table
+------+-------------------------+---------+------------------+------------------+-------------------------+---------- | pkey | email | domain | rule_id | rule_type | log_time | count +------+-------------------------+---------+------------------+------------------+-------------------------+----------- | 1 | user1@yopmail.com | user1 | 566 | type1 | 2016-09-13 17:23:02.000 | 1 | 2 | user2@yopmail.com | user2 | 567 | type2 | 2016-09-13 17:23:02.000 | 1 -----------------------------------------------------------------------------------------------------------------------
suppose if statement below should not create row because same values domain,rule_id,rule_type exists in table need count column increment here
insert rule_reports(domain,rule_id,rule_type,count) values('user1',566,'type1',1)
statement below should create new row in table
insert rule_reports(domain,rule_id,rule_type,count) values('user3',568,'type3',1)
add unique index on columns involved, , use on duplicate key update
increment count if record exists.
alter table rule_reports add unique unique_index(domain, rule_id, rule_type); insert rule_reports (domain, rule_id, rule_type, count) values ('user1', 566, 'type1', 1) on duplicate key update count = count + 1
Comments
Post a Comment