[AccessD] Oracle Pass Thru

I would guess that your (SELECT ... ) AS B has on average about 58 records for each record 
in your  view A.

-- 
Stuart

On 12 Jun 2012 at 5:29, Benson, William (GE Global Re wrote:

> By the bye, when the first query finished, even through there were
> only 139 rows in the table being update, Access said that 8089 rows
> were going to be updated. I have often run into this with update
> queries. 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Benson, William (GE Global Research, consultant)
> Sent: Tuesday, June 12, 2012 1:06 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Oracle Pass Thru
> 
> I am a little ashamed at asking so many Oracle query syntax questions, I hope to be out of the woods soon. I would like either to make the non-pass thru query run faster or solve the syntax problem with the pass thru.
> 
> 
> Non-pass-thru: The following query, with linked tables
> Solarconnect_SID_consolidated_gib_view  and
> SOLARCONNECT_GIBIX_CUSTOMER_HIERARCHY seems to run, but it takes a
> long time. Presumably because they are odbc linked tables, but
> possibly also by poor query construction. 
> 
> 
> 
> UPDATE Solarconnect_SID_consolidated_gib_view AS A INNER JOIN 
> 	(SELECT
> 		 SOLARCONNECT_SID_CONSOLIDATED_GIB_VIEW.SITE_DUNS_NO, 
> 		DOM.PARENT_CUSTOMER_DUNS
> 	FROM 	
> 		SOLARCONNECT_SID_CONSOLIDATED_GIB_VIEW 
> 	INNER JOIN 
> 		SOLARCONNECT_GIBIX_CUSTOMER_HIERARCHY AS DOM 
> 	ON 
> 		SOLARCONNECT_SID_CONSOLIDATED_GIB_VIEW.SITE_DUNS_NO = DOM.CHILD_CUSTOMER_DUNS
> 	WHERE 
> 			DOM.PARENT_CUSTOMER_TYPE="DOM" 
> 		AND 
> 			DOM.CHILD_CUSTOMER_TYPE="STE"
> 	)  AS B 
> 
> ON 
> 
> 	A.Site_duns_no = B.Site_duns_no 
> SET	 A.dom_customer_duns_no = B.parent_customer_duns;
> 
> 
> I tried making it a pass thru, but then the syntax was rejected (note that when a passthru, the SOLARCONNECT prefix is not needed, that is only for linked tables):
> 
> 
> UPDATE SID_consolidated_gib_view A
> INNER JOIN
> (
> SELECT X.SITE_DUNS_NO, DOM.PARENT_CUSTOMER_DUNS FROM SID_CONSOLIDATED_GIB_VIEW X LEFT OUTER JOIN GIBIX_CUSTOMER_HIERARCHY DOM ON X.SITE_DUNS_NO = DOM.CHILD_CUSTOMER_DUNS WHERE DOM.CHILD_CUSTOMER_TYPE='STE'
> AND
> DOM.PARENT_CUSTOMER_TYPE='DOM'
> 
> )  B 
> 
> 
> 
> ON A.Site_duns_no = B.Site_duns_no 
> 
> SET A.dom_customer_duns_no = B.parent_customer_duns;
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 

--
Stuart McLachlan

Ph:    +675 340 4392 
Mob: +675 7100 2028
Web: http://www.lexacorp.com.pg