lundi 22 février 2016

ON DUPLICATE KEY UPDATE - Condition WHERE vs CASE WHEN vs IF?

I am refering to this post. I am stuck with a problem I can't resolve. I try to insert multiple rows with a php script into a MySQL database. I don't succeed in updating the whole thing using ON DUPLICATE KEY UPDATE and using a WHERE condition (at the end of the code below) I would like to use to update only an entry has been modified recently:

// for information (used in a foreach loop):
$args[] = '("'.$row['lastname'].'", '.$row['phone'].', "'.$row['lastModification'].'")';

// then:
$stringImplode = implode(',', $args);

// Where I am stuck - WHERE statement:
$sql = $mysqli->query('INSERT INTO table_name '. (lastname, phone, timestamp) .' VALUES '.$stringImplode .'ON DUPLICATE KEY UPDATE lastname=VALUES(lastname), phone=VALUES(phone) WHERE timestamp > VALUES(lastModification);

Everything works fine except I cannot set any WHERE condition at this point that involves multiples entries. Maybe the WHERE statement in this case is not intended to refer to a condition in this statement.

I was told to try with a database procedure using a JOIN statement and a temporary table with first all my entries and then querying some conditions. But I have to admit I don't understand very well how I could leverage such a table to update an other table.

Is there an easy and lovely way to use a "CASE WHEN" or an "IF" statement in this case?

Would something like

INSERT INTO ... ON KEY DUPLICATE UPDATE lastname = VALUES(lastname), phone = VALUES(phone) 
CASE WHEN (timestamp > VALUES(lastModification)) THEN do nothing ...

or

...ON KEY DUPLICATE UPDATE... IF (timestamp > VALUES(lastModification)) ...

If anyone could help me, I would be very grateful.

EDIT: Since I will have many variables, could it be used in this way:

INSERT INTO ... ON KEY DUPLICATE UPDATE

IF(timestamp > VALUES(timestamp),
    (
    name = VALUES(name),
    number = VALUES(number),
    timestamp = VALUES(timestamp)
    ....many other variables
    ),
    (
    name = name,
    number = number,
    timestamp = timestamp
    ....many other variables)
    )

Aucun commentaire:

Enregistrer un commentaire