Update one datasource using a 2nd datasource?

[Libby]

I have a CF app using an Access 2000 DB, Treasury. One of the tables holds
user data (i.e. location, city, state, supervisor, etc.). This data is updated
sporadically. I have been given a view for an Oracle DB, WebPhone, which has
this information that is updated daily. I would like to update the Treasury
table with the information from the WebPhone DB. I can query both DB?s:

<cfquery name="GetUserInfo" datasource="treasury">
SELECT sbcuid, last, first, add1, add2, city,
state, zip, phone, fax, email, sprvsr_id
FROM sbcuid2
WHERE status <> 'No'
</cfquery>

<cfquery name="GetWebPhone" datasource="WebPhone">
select sbcuid, last_name, UNOFFICIAL_FIRST_NAME, WORK_STREET_ADDR_1,
WORK_STREET_ADDR_2,
WORK_city, WORK_state, WORK_zip, WORK_phone, WORK_fax, EMAIL_ALIAS,
SUPERVISOR_ID
from web_phone_view
WHERE LOWER('#getuserinfo.sbcuid#') = sbcuid
</cfquery>

I have tried a qoq, but was returned an ?update not supported? message. This
is what I want to do:

update sbcuid2
set
#getuserinfo.last# = #GetWebPhone.last_name#
#getuserinfo.first# = #GetWebPhone.UNOFFICIAL_FIRST_NAME#
#getuserinfo.add1# = #GetWebPhone.WORK_STREET_ADDR_1#
#getuserinfo.add2# = #GetWebPhone.WORK_STREET_ADDR_2#
#getuserinfo.city# = #GetWebPhone.WORK_city#
#getuserinfo.state# = #GetWebPhone.WORK_state#
#getuserinfo.zip# = #GetWebPhone.WORK_zip#
#getuserinfo.phone# = #GetWebPhone.WORK_phone#
#getuserinfo.fax# = #GetWebPhone.WORK_fax#
#getuserinfo.EMAIl# = #GetWebPhone.EMAIL_ALIAS#
#getuserinfo.sprvsr_id# = #GetWebPhone.SUPERVISOR_ID#
WHERE LOWER('#getuserinfo.sbcuid#') = '#GetWebPhone.sbcuid#'

Any ideas? Any help is greatly appreciated! :D

[paross1]

One possible way (that would be independent of CF) would be to use ODBC to link
your Oracle table/view within Access. Then, you could actually treat the linked
Oracle table as an Access table within your Access DSN so that you could join
the tables in the same query.

Otherwise, you could select from your Access database, then use CFLOOP or
CFOUTPUT to loop through each selected row to select from your Oracle database,
then Update the particular Access row. Not very efficient, but it should work.

Phil



<cfquery name="GetUserInfo" datasource="treasury">
SELECT sbcuid, last, first, add1, add2, city,
state, zip, phone, fax, email, sprvsr_id
FROM sbcuid2
WHERE status <> 'No'
</cfquery>

<cfoutput query="GetUserInfo">

<cfquery name="GetWebPhone" datasource="WebPhone">
select sbcuid, last_name, UNOFFICIAL_FIRST_NAME, WORK_STREET_ADDR_1,
WORK_STREET_ADDR_2,
WORK_city, WORK_state, WORK_zip, WORK_phone, WORK_fax, EMAIL_ALIAS,
SUPERVISOR_ID
from web_phone_view
WHERE LOWER('#getuserinfo.sbcuid#') = sbcuid
</cfquery>

<cfquery name="UpdUserInfo" datasource="treasury">
update sbcuid2
set
last = #GetWebPhone.last_name#
first = #GetWebPhone.UNOFFICIAL_FIRST_NAME#
add1 = #GetWebPhone.WORK_STREET_ADDR_1#
add2 = #GetWebPhone.WORK_STREET_ADDR_2#
city = #GetWebPhone.WORK_city#
state = #GetWebPhone.WORK_state#
zip = #GetWebPhone.WORK_zip#
phone = #GetWebPhone.WORK_phone#
fax = #GetWebPhone.WORK_fax#
EMAIl = #GetWebPhone.EMAIL_ALIAS#
sprvsr_id = #GetWebPhone.SUPERVISOR_ID#
WHERE LOWER(sbcuid) = '#GetWebPhone.sbcuid#'

</cfoutput>