Page 1 of 1

[SOLVED] mysqli UPDATE ON DUPLICATE KEY using a condition

Posted: Sun Jun 19, 2016 11:56 am
by charp23
I am importing data from a delimited file to a temp table and then using the temp table to update another table. This is working just fine.

My problem is that I now need to UPDATE rows only if "date" in the table is greater than the current date. Here's a simplified version of the query I was using until I discovered that mysqli does not support WHERE in the UPDATE clause.

Code: Select all

INSERT INTO table SELECT * FROM temp_table ON DUPLICATE KEY UPDATE
table.first = temp_table.first,
table.last = temp_table.last,
table.date = temp_table.date
WHERE table.date > CURDATE()
Additional research shows that I can add a conditional IF to "table.date = temp_table.date", but that condition only applies to that one field. I suppose I could add the same condition to every field, but I'm wondering if there's a better way to achieve my goal.

Thanks in advance.

Re: mysqli UPDATE ON DUPLICATE KEY using a condition

Posted: Sun Jun 19, 2016 12:31 pm
by requinix
[edit] Nevermind. Misunderstood.

Re: mysqli UPDATE ON DUPLICATE KEY using a condition

Posted: Sun Jun 19, 2016 1:14 pm
by Christopher
charp23 wrote: until I discovered that mysqli does not support WHERE in the UPDATE clause.
Yours is an INSERT statement, not a UPDATE statement which would allow WHERE. Check the docs on INSERT ... ON DUPLICATE KEY UPDATE. I think the WHERE should follow and be in parens.

Re: mysqli UPDATE ON DUPLICATE KEY using a condition

Posted: Mon Jun 20, 2016 2:34 pm
by charp23
Solved my own problem. I was too focused on updating rows based on a conditional. It finally dawned on me that I could restrict the data selected from temp_table using the same condition.

Re: [SOLVED] mysqli UPDATE ON DUPLICATE KEY using a conditio

Posted: Mon Jun 20, 2016 9:47 pm
by Christopher
Sounds better. I try to avoid large, complex or strange SQL as much as possible. You will be happy in a year when you come back and can understand what the query does.