• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

insert before update

 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Howdy,

Quick question: what if I need to update a record but the record doesn't exist? For this, I'll have to do INSERT first obviously. How can I tell if Insert is needed and not UPDATE (and vice versa)?

Thanks
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If your database supports it, you can use a MERGE statement. MERGE is apparently a new feature in the ANSI SQL 2003 standard. So far, Oracle is the only database I know that supports it though (it's not in the docs for MySQl, Postgres, or HSQLDB, at least yet); I think it came in Oracle 9i, it's certainly present in 10g.

http://en.wikipedia.org/wiki/Merge_%28SQL%29

Some databases supply a non-standard way of doing the same thing (it's sometimes refered to as an "upsert"; you would need to check the documentation of your datbase.

If not, you either have to do it as one of:
1. select
2. test
3a. insert -or-
3b. update

-or-

1. update
2. if error, insert

-or-

1. insert
2. if error, update

Some folks write stored procedures to do that...
 
Peter Primrose
Ranch Hand
Posts: 755
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
excellent.
thanks a lot
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic