Page 1 of 1

REPLACE Into help

Posted: Thu Oct 28, 2010 7:04 pm
by mrlayance
Need some help, can anyone see what I am doing wrong? Keeps erroring out on WHERE `id` = '21'

Code: Select all

REPLACE INTO dealinfo (`site`, `city`, `deal`, `afflink`) VALUES ('buy', 'Halifax', '$title', 'test') WHERE `id` = '21';

Re: REPLACE Into help

Posted: Thu Oct 28, 2010 7:14 pm
by mikosiko
WHERE is not part of the REPLACE syntax
http://dev.mysql.com/doc/refman/5.0/en/replace.html

and not being part of SQL standard you better use INSERT instead that will make your code more portable.. alternative... use INSERT...ON DUPLICATE KEY UPDATE

Re: REPLACE Into help

Posted: Thu Oct 28, 2010 7:19 pm
by mrlayance
mikosiko wrote:WHERE is not part of the REPLACE syntax
http://dev.mysql.com/doc/refman/5.0/en/replace.html

and not being part of SQL standard you better use INSERT instead that will make your code more portable.. alternative... use INSERT...ON DUPLICATE KEY UPDATE
Ok, that makes sense.

Code: Select all

INSERT dealinfo (`site`, `city`, `deal`, `afflink`) VALUES ('buy', 'Halifax', '$title', 'test') ON DUPLICATE KEY UPDATE `id` = '21' 
Just adds a new row? Nothing is replaced?

Re: REPLACE Into help

Posted: Thu Oct 28, 2010 7:24 pm
by Eran
The unique column needs to be a part of the INSERT columns. Why don't you tell us simply what you are trying to achieve?

Re: REPLACE Into help

Posted: Thu Oct 28, 2010 7:38 pm
by mrlayance
Eran wrote:The unique column needs to be a part of the INSERT columns. Why don't you tell us simply what you are trying to achieve?
Basically I have a script that runs and scans an RSS feed for the latest feed headline. The headline is written to a database with unique id and info (Site, City, Headline, Afflink(currently hardcoded, working on lookup table).

Now next time the script updates I want to replace the info in a row based on the unique id, for example id 21.

Re: REPLACE Into help

Posted: Thu Oct 28, 2010 7:56 pm
by Eran

Code: Select all

INSERT INTO dealinfo (`id`,`site`, `city`, `deal`, `afflink`) VALUES (21,'buy', 'Halifax', '$title', 'test') 
   ON DUPLICATE KEY UPDATE `site`=VALUES(`site`),`city`=VALUES(`city`),`deal`=VALUES(`deal`),`afflink`=VALUES(`afflink`)
The unique column (id) is one of the insert columns. Since it already exists (duplicate key) the values in the UPDATE part will be updated

Re: REPLACE Into help

Posted: Thu Oct 28, 2010 8:23 pm
by mrlayance
Eran wrote:

Code: Select all

INSERT INTO dealinfo (`id`,`site`, `city`, `deal`, `afflink`) VALUES (21,'buy', 'Halifax', '$title', 'test') 
   ON DUPLICATE KEY UPDATE `site`=VALUES(`site`),`city`=VALUES(`city`),`deal`=VALUES(`deal`),`afflink`=VALUES(`afflink`)
The unique column (id) is one of the insert columns. Since it already exists (duplicate key) the values in the UPDATE part will be updated
You are a master! Thank you!