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

Question

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.

I don't think you can do anything like this; ON DUPLICATE KEY UPDATE is specific syntax. What is your goal? — Explosion Pills
– Explosion Pills, Commented Dec 22, 2013 at 1:04
@JustinWood This is not about dates but about the SQL statement and how to use on duplicate key with another value.I replaced the dates with 4=4 to simplify the question. — Kallewallex
– Kallewallex, Commented Dec 22, 2013 at 1:05
@ExplosionPills Basically I have a table where votes are saved. The user has the option to change his vote. (I know I could archive this with using SELECT and then INPUT or UPDATE) but this seems inefficient. BUT the user can only change his vote to a specific date which is saved in the db entry. — Kallewallex
– Kallewallex, Commented Dec 22, 2013 at 1:08
@Kallewallex I have never done this, but it might work. Try a normal select query inside the if statement. Like so: IF((SELECT mutable2x.datetime FROM mutable2x) > VALUES(datetime), 7, unj) with the brackets. — Mr. Radical
– Mr. Radical, Commented Dec 22, 2013 at 2:11

Bill Karwin · Accepted Answer · 2013-12-22 01:17:49Z

6

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.

answered Dec 22, 2013 at 1:17

Bill Karwin

567k87 gold badges710 silver badges871 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kallewallex Over a year ago

Beautiful. It works. I knew there was a more efficient way than using SEL+INS/UPD. Thank you very much. PS: My column isn't named "for" I just made all these values and vars up because I am unsure about my contract and the information I am allowed to pass on.

Kallewallex Over a year ago

Is there any way to replace the date (datetime) with datetime from another table? like IF(mutable2x.datetime > VALUES(datetime), 7, uni)

Bill Karwin Over a year ago

@Kallewallex, you might be able to use a scalar subquery, but I haven't tested that. And it would at least work counter to your goal of making an efficient query. :-)

Kallewallex Over a year ago

I am using IF((SELECT mutable2x.datetime FROM mutable2x WHERE mutable2x.hid = $hid) > VALUES(datetime), 7, unj) in my INSERT query because the date is stored in another table. I have no Idea how else to archive this. And it is still more efficient then using SELECT and then INSERT or UPDATE isn't it?

Bill Karwin Over a year ago

Don't count on it being "more efficient" just because one SQL statement is fewer than two SQL statements. You have to measure to be sure. You don't want to be like the boy in the Shel Silverstein poem who traded his two quarters for three dimes, and feels like he came out ahead because three is more than two!

|

Collectives™ on Stack Overflow

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

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related