We help IT Professionals succeed at work.

What is wrong with the following MySQL queries for PMA?

hi there, i have the following queries which are run in succession in PMA. my question is, what is wrong with the middle query - as i do not understand the error message received.

TRUNCATE TABLE dwtphovu_8347379386_test.4_servicesuppliers;

INSERT INTO 
	dwtphovu_8347379386_test.4_servicesuppliers 
	(
		bigint_ServiceID,
		bigint_SupplierID,
		bigint_RegionID
	) 
SELECT 
	(
		SELECT 
			TSC.bigint_ServiceID 
		FROM 
			dwtphovu_8347379386_test.2_servicescatalogue TSC,
			dwtphovu_8347379386_prod.2_servicescatalogue PSC 
		WHERE 
			CONCAT("%",PSC.text_ServiceDescription,"%") LIKE CONCAT("%",TSC.text_ServiceDescription,"%") AND 
			PSC.bigint_ServiceID = PSS.bigint_ServiceID
	), 
	(
		SELECT 
			TS.bigint_SupplierID 
		FROM 
			dwtphovu_8347379386_test.5_suppliers TS,
			dwtphovu_8347379386_prod.5_suppliers PS 
		WHERE 
			CONCAT("%",PS.text_SupplierName,"%" LIKE CONCAT("%",TS.text_SupplierName,"%") AND 
			PS.bigint_SupplierID = PSS.bigint_SupplierID
	), 
	(
		SELECT 
			TR.bigint_RegionID 
		FROM 
			dwtphovu_8347379386_test.1_regions TR,
			dwtphovu_8347379386_prod.1_regions PR 
		WHERE 
			CONCAT("%",PR.text_RegionDescription,"%") LIKE CONCAT("%",TR.text_RegionDescription,"%") AND 
			PR.bigint_RegionID = PSS.bigint_RegionID
	) 
FROM 
	dwtphovu_8347379386_prod.4_servicesuppliers PSS;

SELECT * FROM 
	dwtphovu_8347379386_test.4_servicesuppliers;

Open in new window


the first query is to empty the supplier/service/regional bindings table. the second is to select the data from the production db, but cross referencing service, supplier and region names to get the correct id's in place. the need for this is because these id's are not all the same over the production and test databases - yet all suppliers, regions and services exist in both. the reason for this is because the suppliers and regions tables have been rebuilt in the test system due to upgrades - and in future, quite possibly the services table too.

the error message i am getting is as follows:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
    (
        SELECT
            TR.bigint_RegionID
        FROM
            dwtphovu_8347379386_test.1_regio' at line 28 

Open in new window


any help with regard to getting the second cross table, cross database query to run without problems would be greatly appreciated, thanks.
Comment
Watch Question

duh -_- the CONCAT function wasn't closed :P

Author

Commented:
oh well, sometimes we do not see the most obvious especially in larger amounts of code/queries... ;)