1

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)
    )
Community
  • 1
  • 1
Trichophyton
  • 625
  • 5
  • 21

1 Answers1

2

You can use simple IF function in value like this:

INSERT INTO ... ON KEY DUPLICATE UPDATE
  name = VALUES(name),
  number = VALUES(number),
  timestamp = IF(timestamp > VALUES(timestamp), VALUES(timestamp), timestamp)

If condition is not met, it will update timestamp with the same timestamp which already exists. It does not matter, because update to same values is optimized before it is even executed, so MySQL will not make real update. You should not afraid of some performance penalty.

EDIT: IF works likes this:

IF(condition, returned when true, returned when false)

Maybe you need to switch those two arguments to fit your condition like you want.

micropro.cz
  • 598
  • 4
  • 17
  • Thanks I will have a try as soon as possible!! keep you updated :-) – Trichophyton Feb 22 '16 at 15:06
  • But I think there will be a problem, I would like all the variables to be modified according to the IF statement and not just only the timestamp. Should I move the IF statement somewhere else? I mean var=VALUES(var) IF timestamp>VALUES(timestamp) – Trichophyton Feb 22 '16 at 15:11
  • Something like: INSERT INTO ... ON KEY DUPLICATE UPDATE name = VALUES(name), number = VALUES(number), timestamp = VALUES(timestamp) IF(timestamp > VALUES(timestamp)? Should add a bracket? Or in this case should I do something with a CASE WHEN? – Trichophyton Feb 22 '16 at 15:13
  • You can't modify all variables, because at least PRIMARY KEY column has to be unchanged, otherwise you can't use ON DUPLICATE KEY UPDATE. But you can add IF() condition to other variables too. IF can use other columns in conditions, so you can have condition with timestamp on name column too. There is not another option, because you can't you WHERE on INSERT statement. But IF is exactly what you need and can do it for you. – micropro.cz Feb 22 '16 at 15:14
  • Yes of course I will not change my unique key. But I need to update all other variables if timestamp > VALUES(timestamp). Do I need to add the IF statement as you proposed for every variable value or can I use it only at one point in the query? The unique key will not be included in this query – Trichophyton Feb 22 '16 at 15:17
  • Since I have 20 variables (and probably more in the future), do I have to add an IF statement after every variable like name = IF(timestamp > VALUES(timestamp), VALUES(name), name), number = IF(timestamp > VALUES(timestamp), VALUES(number), number)..... and so on for every variable except the constraint? Or could I use it once for all of them? Thanks for your help. – Trichophyton Feb 22 '16 at 15:20
  • I am afraid there is no other option than this. And you shouldn't mention constraint in UPDATE part at all, it's useless. You should list only columns, you want to eventually update. – micropro.cz Feb 22 '16 at 15:26
  • But it should be possible to save condition result to temporary variable like this: name = IF(@result:=timestamp > VALUES(timestamp), name, VALUES(name)), timestamp = IF(@result, timestamp, VALUES(timestamp)), ... – micropro.cz Feb 22 '16 at 15:27
  • I edited my question with an IF statement at the beginning of the UPDATE. Could it work? I will only mention fields I want to change in the UPDATE part. – Trichophyton Feb 22 '16 at 15:27
  • No, it will not work. IF can return only value, not set of columns. You need to use IF for every column in this case. There is no other option. You can only save condition with temporary variable as I mention above. – micropro.cz Feb 22 '16 at 15:35