-
Title
Can Query Builder update a column in one database platform with values queried from a different -
Description
Can Query Builder update a column in one database platform with values queried from a different database platform? For example, updating a column in an Access table with values queried from an Oracle table.
-
Cause
Access will not allow a subquery in an Update command using pass-through SQL, however, it will work if query is rewritten.
-
Resolution
The Update query cannot be created using the Query Builder. However, a Select statement can be created in the Query Builder and modified it in the SQL Editor in order to be executed in Access. An example is given in the Additional Information section below.
-
Additional Information
This query works when run by itself:
SELECT "PP_USER".USER_NAME
FROM "ODBC;DRIVER={Microsoft ODBC for Oracle};CONNECTSTRING=TMDEV.XX.XXXXX.XX.US;UID=OWNER;PWD=PASSWORD;"."DATABASE.SCHEMA" "USER"
However, when placed into the update statement software does not recognize the connection:
UPDATE WAREHOUSE
SET WAREHOUSE_NAME =
(SELECT "PP_USER".USER_NAME ...
FROM "ODBC;DRIVER={Microsoft ODBC for Oracle};CONNECTSTRING=TMDEV.XX.XXXXX.XX.US;UID=OWNER;PWD= ...
Access will not allow a sub query in an Update command using pass-through SQL, however, it will work if the user rewrites the query as follows:
UPDATE `U:\Dev\Source\Toad\bin\Debug\Plugins\Odbc\Sample Database\Quest_Stage`.ADDRESS ADDRESS
INNER JOIN
{{Odbc("DRIVER={Microsoft ODBC for Oracle};CONNECTSTRING=DEB10G.PROD.QUEST.COM;UID=DEB;PWD={0.EN};,D9B9460730671CCB")}}."QUEST_STAGE.ADDRESS" ADDRESS1
ON (CVAR(ADDRESS1.ADDRESS_ID) = CVAR(ADDRESS.ADDRESS_ID))
SET ADDRESS.ADDRESS = ADDRESS1.ADDRESS
This query can not be created using the Query Builder but the user can create a SELECT statement in the Query Builder and modify it in the SQL Editor to match the format above.