It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.
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.
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'
";
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Something is wrong with this queryWhat is the Something in that statement? Please tell us a little more. Is the column country an index? Is there an error message?
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='13564800
country is an index.
Corrected an error, but still the same error message
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='13564800
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'
";
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
The "Is the column country an index?" question kinda 'tricked' it for me, think I fixed the issue. This seems to work:
I guess "ON DUPLICATE country UPDATE value='$item', updated=....." is not valid.
$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)."'
";
I guess "ON DUPLICATE country UPDATE value='$item', updated=....." is not valid.
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
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial