Solved

Problem with ON DUPLICATE query

Posted on 2012-12-25
9
235 Views
Last Modified: 2012-12-30
Hi,

Something is wrong with this query, but don't know what :(
The query should update an entry if it already exists (country code) or insert it not.

$sql = "
INSERT INTO currency_exchange_rates
	(base, country, value, updated, updated_datetime) 
VALUES
	('{$this->base}', '$code','$item','{$exchangeRates->timestamp}','".date("Y-m-d H:i:s",$exchangeRates->timestamp)."')
ON DUPLICATE
	country
UPDATE
	value='$item', updated_datetime='{$exchangeRates->timestamp}', updated_datetime='".date("Y-m-d H:i:s",$exchangeRates->timestamp)."'
WHERE
	country='$code'
";

Open in new window

0
Comment
Question by:kgp43
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
9 Comments
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 38720435
Something is wrong with this query
What is the Something in that statement?  Please tell us a little more.  Is the column country an index?  Is there an error message?
0
 
LVL 3

Expert Comment

by:wadehults
ID: 38720444
Here is an article that may be useful to you:
Duplicate Key Update
0
 

Author Comment

by:kgp43
ID: 38720470
Ray_Paseur;

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'country UPDATE value='3.672971', updated_datetime='1356480009', updated_d' at line 6

country is an index.

Corrected an error, but still the same error message
$sql = "
INSERT INTO currency_exchange_rates
	(base, country, value, updated, updated_datetime) 
VALUES
	('{$this->base}', '$code','$item','{$exchangeRates->timestamp}','".date("Y-m-d H:i:s",$exchangeRates->timestamp)."')
ON DUPLICATE
	country
UPDATE
	value='$item', updated='{$exchangeRates->timestamp}', updated_datetime='".date("Y-m-d H:i:s",$exchangeRates->timestamp)."'
WHERE
	country='$code'
";

Open in new window


You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'country UPDATE value='3.672971', updated='1356480009', updated_datetime='' at line 6

Open in new window

0
Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

 

Author Comment

by:kgp43
ID: 38720479
The "Is the column country an index?" question kinda 'tricked' it for me, think I fixed the issue. This seems to work:

$sql = "
INSERT INTO currency_exchange_rates
	(base, country, value, updated, updated_datetime) 
VALUES
	('{$this->base}', '$code','$item','{$exchangeRates->timestamp}','".date("Y-m-d H:i:s",$exchangeRates->timestamp)."')
ON DUPLICATE KEY UPDATE
	value='$item', updated='{$exchangeRates->timestamp}', updated_datetime='".date("Y-m-d H:i:s",$exchangeRates->timestamp)."'
";

Open in new window


I guess "ON DUPLICATE country UPDATE value='$item', updated=....." is not valid.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38720498
Make a Google search for "on duplicate key update mysql php" and see if the answers win the day.  Best regards and merry Christmas, ~Ray
0
 
LVL 3

Expert Comment

by:wadehults
ID: 38720585
The only thing I have to add to this is that the ON DUPLICATE KEY UPDATE command line only works with Unique or Primary Key values.
0
 

Author Comment

by:kgp43
ID: 38720791
Thanks Wadehults, will make sure that is the case :)
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 38721043
Man Page here: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
Important Note here:  In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

That tells me that you want country to be the index and the only index.
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to implement server side field validation and display customized error messages to the client.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question