insert into select on duplicate mysql query

问题:

I am trying to update on duplicate record in MySQL,

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.

my query is:

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;

another query i tried

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.?

回答1:

You could try :

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);

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

回答2:

You say that you are trying to update a record, but you are using an INSERT statement. Shouldn't you be using UPDATE instead of INSERT?

Difference between INSERT and UPDATE can be found here

Note that you can use UPDATE and SELECT in a single query.

回答3:

try this. its worked for me.

Insert into employee_projects(employee_id,proj_ref_code) select ep.employee_id,ep.proj_ref_code from hs_hr_emp_projects_history ep where not exists ( select 1 from employee_projects p where ep.employee_id = p.employee_id and ep.proj_ref_code = p.proj_ref_code )