CODEWITHSUNDEEP
phpmysqlpdosql-updateduplicates
gmaestro
gmaestro

Reputation: 339

Insert or update on duplicate with two keys

Here I have this mysql datatable:

enter image description here

So I want update when I have duplicate of ID_polja and datum so when Id_polja and datum are the same if not then just add new row... Is there any php pdo query to do this?

I try with:

 $STH = $conn->prepare("INSERT INTO vremenska (ID_polja,datum,verov_kisa,mm_kisa) VALUES ( :ID_polja, :datum, :verov_kisa,:mm_kisa) on duplicate key update verov_kisa=values(verov_kisa), mm_kisa=values(mm_kisa)");

this query is ok, but I have a problem, I need to both ID_polja and datum be the same to update if not then to add new row?

How I can do that?

Upvotes: 2

Views: 3144

Answers (1)

spencer7593
spencer7593

Reputation: 108420

If there's a UNIQUE constraint on the combination of the two columns, e.g.

CREATE UNIQUE INDEX tabledata_UX1 ON tabledata (ID_polja,datum) ;

This index won't allow a row to be inserted if the values of the ID_polja and datum columns match another row in the table. (The same value of ID_polja can exist in the table, and the same value of datum can exist... it's the combination of the two columns that has to be unique.)

With a UNIQUE constraint, you can then use an INSERT ... ON DUPLICATE KEY statement, for example:

INSERT INTO tabledata (ID_polja, datum, temp, verov_kisa)
VALUES ('94','2014-05-25',0,0.87)
ON DUPLICATE KEY
UPDATE temp = VALUES(temp)
     , verov_kisa = VALUES(verov_kisa)

A normal INSERT statement would throw an error when the new row attempts to insert a row that matches another row, with the same values in the ID_polja and datum columns.

But this statement, with the ON DUPLICATE KEY clause, catches the "duplicate key" error, and causes an UPDATE to be performed. The update action (in this example) would be equivalent to running this statement:

UPDATE tabledata
   SET temp = 0
     , verov_kisa = 0.87 
 WHERE ID_polja = '94'
   AND datum = '2014-05-25'

The statement will return a rows affected count of 1 for an INSERT action, and a count of 2 for an UPDATE action.

Note that the INSERT ... ON DUPLICATE KEY statement will first attempt an INSERT, and the UPDATE is only performed if the INSERT throws a "duplicate key" exception.

This is only one approach, there are also other (usually less efficient) ways to emulate the same behavior.

Upvotes: 3

Related Questions