List:General Discussion« Previous MessageNext Message »
From:Randall Price Date:April 13 2007 1:54pm
Subject:RE: Is it possible to either update or insert in a single query?
View as plain text  
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
    BEGIN
      UPDATE myTable SET .....
    END;
  END IF;
END;


Randall Price

Secure Enterprise Technology Initiatives
Microsoft Implementation Group
Virginia Tech Information Technology


-----Original Message-----
From: Douglas Pearson [mailto:biz@stripped] 
Sent: Friday, April 13, 2007 2:14 AM
To: mysql@stripped
Subject: Is it possible to either update or insert in a single query?

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 rowsChanged == 0 then run the INSERT 

It just feels like there must be a way to do this more efficiently.

Thanks,

Doug


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=1

Thread
Is it possible to either update or insert in a single query?Douglas Pearson13 Apr
  • Re: Is it possible to either update or insert in a single query?Carlos Proal13 Apr
  • Re: Is it possible to either update or insert in a single query?Mogens Melander13 Apr
    • RE: Is it possible to either update or insert in a single query?Douglas Pearson13 Apr
  • RE: Is it possible to either update or insert in a single query?Randall Price13 Apr
Re: Is it possible to either update or insert in a single query?Martijn Tonies13 Apr