Updating a join view oracle
Whereas UPDATE can only modify column values you can use the MERGE statement to synchronize all data changes such as removal and addition of row. The key items, such as source, target, merge conditions, and the set clause are in order top down.
The MERGE statement is structured to handle all three operations, INSERT, UPDATE, and DELETE, in one command. When you read the UPDATE statement, the key items are scattered.
The illustration in Figure 1 below shows an item master table from Application A along with a supplemental custom item extension table; this table, as the name suggsts, provides supplemental columns not provided by the application’s Item Master.
While it is relatively easy to retrieve and modify the data from both tables using RPG and subfiles, it is not so easy to do with SQL, especially in a client/server application.
Whereas a DELETE would normally fail against such a view, it now succeeds because of the alternative code provided to DB2. The application form is fully functional, with the ability to modify data in both underlying tables, although Access is oblivious to the fact that there are actually two tables being changed.
Of course this is just one possibility, as we could have just as easy coded the “delete” trigger to actually update a deleted flag column to ‘Y’ in the base tables. The beauty here is that both related tables are present in a single form and can be updated simultaneously.
(Instead of letting the database manager to do the modification itself, which would be impossible for it to do! INSTEAD OF triggers can save some integration hassle in this situation.When modifying one table based on the contents of another it may be tempting to simply use the merge statement, but before you throw away all other forms of modifying data, I think it’s important for you to know the difference between merge and update.Both the MERGE and UPDATE statements are designed to modify data in one table based on data from another, but MERGE can do much more.(MSDN: MERGE Transact-SQL) Here is a side-by-side comparison of the MERGE and UPDATE statements: In this side by side comparison you can see the similarities key areas of these statements: With MERGE, you’re able to combine update, delete, and insert command into one statement.
This is because the MERGE statement uses a WHEN clause to determine the course of action to take on the match.
For simplicity, consider the following common scenario: An i Series has multiple item tables holding data related in a one-to-one relationship.