insert into select on duplicate mysql query

Question

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

I cannot reproduce your, problem query 1 works fine for me. Can you publish your table definitions please. — P.Salmon, Jul 28 '17 at 06:58

score 0 · Answer 1 · answered Jul 28 '17 at 06:55

0

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.

answered Jul 28 '17 at 06:55

Hibari

131
16

sir there are lots of rows to be either insert or update depends on realtime situation i can not use update for all of them. so it might not helpful specially in my case. – VasaraBharat Jul 28 '17 at 10:21

score 0 · Accepted Answer · answered Jul 28 '17 at 08:30

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

score 0 · Answer 3 · edited Jun 02 '20 at 17:06

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)

insert into select on duplicate mysql query

3 Answers3

Linked