How do I update two different databases located at two different locations using the SQL UPDATE method?
What you are trying to do is called a Distributed Transaction. Part VI of the Oracle 9i Administrator's Guide (http://technet.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96521/toc.htm) discusses this topic in much detail.
The basic idea is to create a database link from one database to another. You do this as follows:
CREATE DATABASE LINK remote_db CONNECT TO user IDENTIFIED BY pswd USING 'tns_alias';This link tells which system to connect to (tns_alias), which userid in that system (user) and that user's password (pswd). Now that the link is set up, we can access any table in that remote database that the user has permissions to by using the '@link_name' clause on the table name. So to answer your question, we can update a table in another database with a query similar to the following:
UPDATE some_table@remote_db SET colA='some_value' WHERE colB='some_other_value';
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.