REPLACE Into help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
mrlayance
Forum Commoner
Posts: 31
Joined: Mon Dec 07, 2009 11:53 am

REPLACE Into help

Post 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';
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: REPLACE Into help

Post 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
mrlayance
Forum Commoner
Posts: 31
Joined: Mon Dec 07, 2009 11:53 am

Re: REPLACE Into help

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: REPLACE Into help

Post 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?
mrlayance
Forum Commoner
Posts: 31
Joined: Mon Dec 07, 2009 11:53 am

Re: REPLACE Into help

Post 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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: REPLACE Into help

Post 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
mrlayance
Forum Commoner
Posts: 31
Joined: Mon Dec 07, 2009 11:53 am

Re: REPLACE Into help

Post 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!
Post Reply