Add/Update SQL Query

[wige]

Hello everyone. I am pretty much of a novice with MySQL, and I am trying to find out if there is a single query that will update an existing record, or if the record does not exist, create the record. I know I could do somthing like

$query = "UPDATE table SET name = 'new' WHERE id = '123'";
$result = @mysql_query($query);
if (!$result) {
   $query = "INSERT INTO table (name, id) VALUES ('new', '123')";
   $result = @mysql_query($query);
}

But is there a way to accomplish the same thing with a single database query?

[brian.mark]

Sure. The action is "REPLACE INTO".

Originally Posted by MySQL.com

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

Source

Brian.

[mikesmith76]

Sure. The action is "REPLACE INTO".

Keep in mind though that this is MySQL specific (AFAIK). May be a pitfall if you ever need to move to another database system

[wige]

Ok. Thanks!

[mushroom]

Originally Posted by brian.mark

Sure. The action is "REPLACE INTO".

Source

Brian.

Not always the best thing to use as any fields that are not included in the query are set to their default values.

[brian.mark]

Originally Posted by mushroom

Not always the best thing to use as any fields that are not included in the query are set to their default values.

Good point there. As I said, the old row is deleted before the new row is inserted. No values come across.

Brian.