CODEWITHSUNDEEP
phpmysql
Hoda Kh
Hoda Kh

Reputation: 355

update existing record in MySQL when inserting

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?

Upvotes: 0

Views: 142

Answers (2)

Mjh
Mjh

Reputation: 2945

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

Upvotes: 4

Gops
Gops

Reputation: 26

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

With Stored Procedures.

Upvotes: -3

Related Questions