PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Oct 17, 2017 11:41 am

All times are UTC - 5 hours




Post new topic Reply to topic  [ 5 posts ] 
Author Message
PostPosted: Sun Jun 19, 2016 11:56 am 
Offline
Forum Newbie

Joined: Fri Jun 27, 2014 9:23 am
Posts: 9
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.

Syntax: [ Download ] [ Hide ]
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.

Top
 Profile  
 
PostPosted: Sun Jun 19, 2016 12:31 pm 
Offline
Spammer :|
User avatar

Joined: Wed Oct 15, 2008 2:35 am
Posts: 6571
Location: WA, USA
[edit] Nevermind. Misunderstood.


Top
 Profile  
 
PostPosted: Sun Jun 19, 2016 1:14 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13434
Location: New York, NY, US
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)


Top
 Profile  
 
PostPosted: Mon Jun 20, 2016 2:34 pm 
Offline
Forum Newbie

Joined: Fri Jun 27, 2014 9:23 am
Posts: 9
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.


Top
 Profile  
 
PostPosted: Mon Jun 20, 2016 9:47 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13434
Location: New York, NY, US
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)


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 7 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group