You can use subqueries in the SET portion of the UPDATE command.
The FROM clause of such subqueries can reference either the local database or a remote database.
Consider another table STUDENT_N, which holds updates for the target table STUDENT.
Every week, table STUDENT needs to be synchronized with the data in STUDENT_N – any new entries for students who attempted the GMAT to be inserted, plus corrections if any made to the existing details.
This is typically the case when you have to synchronize a table periodically with data from another source (table/view/query).
In place of 3 separate unwieldy INSERT, UPDATE and DELETE statements with conditional sub-queries, the all-in-one MERGE does the job in one shot.
Views, which are a type of virtual tables allow users to do the following − Database views are created using the CREATE VIEW statement.
Views can be created from a single table, multiple tables or another view.
For example, to change the RATING values for books in a remote BOOKSHELF table, you would execute the UPDATE command shown in the following listing: This UPDATE command uses the REMOTE_CONNECT database link to log into the remote database.
It then updates the BOOKSHELF table in that database, based on the SET and WHERE conditions specified.
The database link syntax for remote updates is the same as that for remote queries.