Mysql - 基于其他表插入条件 - ON DUPLICATE KEY UPDATE

UPDATE

UPDATE

I found the answer and provided below

我找到了答案并在下面提供

Dear All,

亲爱的大家，

I want to insert into a table 1) Based on condition on other table 2) and using ON DUPLICATE KEY UPDATE on the first table.

我想插入表1）基于其他表2）的条件并在第一个表上使用ON DUPLICATE KEY UPDATE。

The following query I wrote which is syntactically wrong . Could you please help me on the correct query for this ?

我写的以下查询语法错误。你能帮我解决一下这个问题吗？

INSERT INTO my_all_count (type,code,count) values ( 0,1,1) 
ON DUPLICATE KEY UPDATE count = count + 1 
WHERE NOT EXISTS ( 
  select 1 from my_reg_count 
  where country_code=CurrCountry and type=0 and code=0);

here 1) I want to insert into table my_all_count 2) type,code is a key and if it exists increasing county by 1 3) Insert only when it is not exists in my_reg_count

这里1）我想插入表my_all_count 2）类型，代码是一个键，如果它存在增加县1 3）插入只有当它不存在于my_reg_count

Thanks for your help

谢谢你的帮助

Regards

问候

Kiran

基兰

2 个解决方案

#1

I found the solution and here is the answer

我找到了解决方案，这就是答案

INSERT INTO my_all_count (type,code,count) 
    select 0,0,1 from dual WHERE NOT EXISTS ( 
         select * from my_reg_count where country_code=CurrCountry 
          and type=0 and code=0) ON DUPLICATE KEY UPDATE count = count + 1;

#2

Not sure this will work, but I am sure the ON DUPLICATE KEY clause should be after the WHERE clause:

不确定这会有效，但我确定ON DUPLICATE KEY子句应该在WHERE子句之后：

INSERT INTO my_all_count (type,code,count) values (0,1,1) 
WHERE NOT EXISTS ( 
  select 1 from my_reg_count 
  where country_code=CurrCountry and type=0 and code=0)
ON DUPLICATE KEY UPDATE count = count + 1;

#1