Apologies if this is a dumb question, but is it possible to write a single
query that either updates certain columns in a row, or adds an entirely new
row if there is none already?
I seem to be running into this a lot, and so far I've solved it by:
1) run UPDATE table SET x,y WHERE some row
2) if
Have you look at replace ?
http://dev.mysql.com/doc/refman/5.0/en/replace.html
Carlos
Douglas Pearson wrote:
Apologies if this is a dumb question, but is it possible to write a single
query that either updates certain columns in a row, or adds an entirely new
row if there is none already?
I
This part of the manual might be of use to you:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES ({expr | DEFAULT},...),(...),...
Or:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
Or:
REPLACE [LOW_PRIORITY |
REPLACE is a MySQL extension to the SQL standard. It either inserts, or
deletes and inserts. For another MySQL extension to standard SQL — that
either inserts or updates — see Section 13.2.4.3, “INSERT ... ON DUPLICATE
KEY UPDATE Syntax”.
Take note!!
If you're using triggers, foreign key
You could try a stored procedure that either inserts a new row or
updates an existing row:
CREATE PROCEUDRE InsertOrUpdateRecord(IN NewID INT, ... other params ...
)
BEGIN
IF NOT EXISTS (SELECT ID FROM myTable WHERE ID = NewID) THEN
BEGIN
INSERT INTO myTable ()
END;
ELSE
Thanks Mogens. I was aware of REPLACE but it was the non-standard ON
DUPLICATE KEY UPDATE that I was looking for.
Thanks,
Doug
-Original Message-
From: Mogens Melander [mailto:[EMAIL PROTECTED]
Sent: Friday, April 13, 2007 2:28 AM
To: Douglas Pearson
Cc: [EMAIL PROTECTED]
Subject: Re:
