Update/insert
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Update/insert

  1. #1
    Join Date
    Oct 2000
    Posts
    211
    Hi friends,
    I need to check a table for a record. If the record exists(WHERE clause will include the primary key column), I need to update it and if the record is not there, the entire fresh record need to be inserted. Can some one help me with the query?
    And also what if the select query contains a WHERE on non key column?
    Thanks
    manjunath

    [Edited by manjunathk on 01-17-2001 at 04:39 PM]

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    I did something similar to this a couple of years ago. The requirement was that when a record was inserted into a table if it was a duplicate record (based on the PK) then the record would be updated instead of insert.

    What we had to do was create a view of the base table and create an INSTEAD OF trigger on the view. Inside the INSTEAD OF trigger I performed an insert into the base table. If the insert failed because of a DUP_VAL_ON_INDEX exeption, I updated the base table via the PK with the new values. Then, I inserted data into the view.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Oct 2000
    Posts
    211
    thanks Jeff,
    But what will happen if my initial query does not contain the key column?
    Thanks
    manjunath

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If it doesn't contain they key column, how can you tell if it is a duplicate?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Oct 2000
    Posts
    211
    thanks Jeff.
    _________
    manjunath

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width