update existing record in MySQL when inserting

Question

I have a table in a MySQL database containing below fields:

cid
number
date
code

cid and number are keys. Every day data from another table from the first date is inserted to this table with a php script. code field is updatable in source table and I want to update the existing records in destination table if code has changed when inserting records to destination. I mean I do not want to insert updated data as a new row but updating current record.

How can I do this?

@BeetleJuice I do not know how to update existing row when inserting updated data in MySQL. — Hoda Kh, Aug 09 '16 at 13:39
Possible duplicate of [How to 'insert if not exists' in MySQL?](http://stackoverflow.com/questions/1361340/how-to-insert-if-not-exists-in-mysql) — cteski, Aug 09 '16 at 13:48

score 4 · Answer 1 · answered Aug 09 '16 at 14:16

You do this by using MySQL's INSERT INTO ... ON DUPLICATE KEY UPDATE.

For this to work, you must set a UNIQUE index so that MySQL produces an error (because record exists) and then it moves on to the UPDATE part.

In your case, I assume that this key would be UNIQUE(cid, number, date).

Given your current table, query would be like this:

INSERT INTO your_table_name 

(cid, number, date, code) 

VALUES 

(1, 2, '2016-08-09', 'code') 

ON DUPLICATE KEY SET code = 'code';

Adjust column names and values accordingly.

Documentation about ON DUPLICATE KEY UPDATE

score -3 · Answer 2 · edited Aug 09 '16 at 13:59

-3

IF EXISTS...
THEN
UPDATE ... SET ....
ELSE 
INSERT INTO ...

With Stored Procedures.

edited Aug 09 '16 at 13:59

nalzok

11,870
17
57
105

answered Aug 09 '16 at 13:47

Gops

26
2

update existing record in MySQL when inserting

2 Answers2