REPLACE only part of a row

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
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

REPLACE only part of a row

Post by superdezign »

The REPLACE action makes it so that instead of querying to see if a row exists before inserting/updating, I can do it all in one query. However, I can't keep any of the data the same.

Code: Select all

REPLACE `table` (`primary`, `newstuff`, `oldstuff`) VALUES ($id, $newStuff, `oldstuff`);
This doesn't keep what's in the `oldstuff` column. Is there any way to do this without breaking it up into multiple queries?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I don't think there is. You can't really use IF NOT EXISTS update else insert in MySQL. If there is a way I would love to know. Your best course of action is to probably use a stored procedure.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What about INSERT..ON DUPLICATE KEY...?
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

INSERT INTO t (a,b,c,d,e) VALUES (1,2,3,4,5)
ON DUPLICATE KEY UPDATE c=3,d=4,e=5;
Might work for you. (From user comments: http://dev.mysql.com/doc/refman/5.0/en/ ... icate.html) I've looked into that before but it wouldn't work in my situation.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Oooh. Nice. :D
Post Reply