CODEWITHSUNDEEP
phpmysql
Kallewallex
Kallewallex

Reputation: 521

PHP - MYSQL - insert on duplicate key AND(?)

Is it possible to connect the duplicate key to another statement. I just picked some integers (4=4) for the example. In the actuall code I am trying to compare two dates and only if the date in the database row is bigger than the php generated date AND duplicated key it should update unj to 7.

from this:

$sql="INSERT INTO mutable (hid, xsn, unj, for, datetime)
VALUES ('$hid', '$xsn', '$unj', '$for', now()) ON DUPLICATE KEY UPDATE unj=7";

to this:

 $sql="INSERT INTO mutable (hid, xsn, unj, for, datetime)
VALUES ('$hid', '$xsn', '$unj', '$for', now()) ON 4=4 AND DUPLICATE KEY UPDATE unj=7";

( ON 4=4 AND ) added.

But this is not working. Is there any way to archive this?

Thank you.

edit: I know I could archive this with using SELECT and then INPUT or UPDATE but I need more efficient code.

Upvotes: 1

Views: 1233

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562931

INSERT INTO mutable (hid, xsn, unj, `for`, datetime)
VALUES ('$hid', '$xsn', '$unj', '$for', now()) 
ON DUPLICATE KEY UPDATE unj = IF(datetime > VALUES(datetime), 7, unj)

I tested this and it works.

The VALUES(datetime) refers to the value you tried to insert into the datetime column. It's a convenient way to repeat the value in your ON DUPLICATE KEY UPDATE clause without having to write it twice in the query.

If the condition in IF() returns false, then the default is to set unj = unj which means a no-op.

PS: for is a MySQL reserved word, so it needs to be delimited. It would be simpler to avoid that column name.

Upvotes: 6

Related Questions