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
– Hoda Kh, Commented Aug 9, 2016 at 13:39
Possible duplicate of How to 'insert if not exists' in MySQL? — cteski
– cteski, Commented Aug 9, 2016 at 13:48

Mjh · Accepted Answer · 2016-08-09 14:16:27Z

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

nalzok · Accepted Answer · 2016-08-09 13:59:52Z

-3

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

With Stored Procedures.

edited Aug 9, 2016 at 13:59

nalzok

16.4k24 gold badges85 silver badges159 bronze badges

answered Aug 9, 2016 at 13:47

Gops

262 bronze badges

Collectives™ on Stack Overflow

update existing record in MySQL when inserting

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related