[SOLVED] mysqli UPDATE ON DUPLICATE KEY using a condition

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
charp23
Forum Newbie
Posts: 9
Joined: Fri Jun 27, 2014 9:23 am

[SOLVED] mysqli UPDATE ON DUPLICATE KEY using a condition

Post 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.
Last edited by charp23 on Mon Jun 20, 2016 2:35 pm, edited 1 time in total.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: mysqli UPDATE ON DUPLICATE KEY using a condition

Post by requinix »

[edit] Nevermind. Misunderstood.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: mysqli UPDATE ON DUPLICATE KEY using a condition

Post 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.
(#10850)
charp23
Forum Newbie
Posts: 9
Joined: Fri Jun 27, 2014 9:23 am

Re: mysqli UPDATE ON DUPLICATE KEY using a condition

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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.
(#10850)
Post Reply