在重复的mysql查询上插入选择 [英] insert into select on duplicate mysql query

问题描述

我正在尝试更新MySQL中的重复记录，

I am trying to update on duplicate record in MySQL,

我的表中有很多列，但是我只想更新另一个与当前表具有相同desc的表中的某些列，但是它不更新记录.

I have a table with many column but i want to update only some column from another table with same desc as current table but it is not updating records.

我的查询是:

插入backup_prochart.symbol_list(ticker，end_date，cur_date) 从prochart.symbol_list t中选择代码，t.end_date，t.cur_date，其中 重复键更新上的股票代码= t.ticker和股票代码='MAY17' end_date = t.end_date，cur_date = t.cur_date;

insert into backup_prochart.symbol_list(ticker,end_date,cur_date) select ticker,t.end_date,t.cur_date from prochart.symbol_list t where ticker=t.ticker and ticker= 'MAY17' on duplicate key update end_date=t.end_date,cur_date=t.cur_date;

我尝试过的另一个查询

插入backup_prochart.symbol_list(ticker，end_date，cur_date)，从prochart.symbol_list t中选择t.ticker，t.end_date，t.cur_date，其中重复键更新时，ticker = t.ticker和t.ticker ='MAY17' end_date = t.end_date，cur_date = t.cur_date;

insert into backup_prochart.symbol_list(ticker,end_date,cur_date) select t.ticker,t.end_date,t.cur_date from prochart.symbol_list t where ticker=t.ticker and t.ticker= 'MAY17' on duplicate key update end_date=t.end_date,cur_date=t.cur_date;

谁能告诉我我的查询出了什么问题??

can anyone tell me whats wrong with my query.?

推荐答案

您可以尝试:

INSERT INTO backup_prochart.symbol_list (ticker, end_date, cur_date) 
SELECT ticker, end_date, cur_date FROM prochart.symbol_list WHERE ticker = 'MAY17' 
ON DUPLICATE KEY UPDATE end_date = values(end_date), cur_date = values(cur_date);

当然，对于表"backup_prochart.symbol_list"，"ticker"列必须定义为唯一.

Of course the column "ticker" must be defined as unique for the table "backup_prochart.symbol_list".

这篇关于在重复的mysql查询上插入选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！