The database link syntax for remote updates is the same as that for remote queries. Append the name of the database link to the name of the table being updated. 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.
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. To refer to the remote database in a subquery, append the database link name to the table names in the FROM clause of the subquery. An example of this is shown in the following listing:
NOTE
If you do not append the database link name to the table names in the FROM clause of UPDATE subqueries, tables in the local database will be used. This is true even if the table being updated is in a remote database.
In this example, the remote BOOKSHELF table is updated based on the RATING value on the remote BOOKSHELF table. If the database link is not used in the subquery, as in the following example, then the BOOKSHELF table in the local database is used instead. If this is unintended, it will cause local data to be mixed into the remote database table. If you are doing this on purpose, be very careful.
Leave a Reply