• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 238
  • Last Modified:

Problem with ON DUPLICATE query

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
kgp43
Asked:
kgp43
  • 3
  • 3
  • 2
1 Solution
 
Ray PaseurCommented:
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
 
wadehultsCommented:
Here is an article that may be useful to you:
Duplicate Key Update
0
 
kgp43Author Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kgp43Author Commented:
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
 
Ray PaseurCommented:
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
 
wadehultsCommented:
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
 
kgp43Author Commented:
Thanks Wadehults, will make sure that is the case :)
0
 
Ray PaseurCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now