Update/insert

[manjunathk]

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]

[marist89]

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.

[manjunathk]

thanks Jeff,
But what will happen if my initial query does not contain the key column?
Thanks
manjunath

[marist89]

If it doesn't contain they key column, how can you tell if it is a duplicate?

[manjunathk]

thanks Jeff.
_________
manjunath