UPDATING TABLE B TO VALUES FROM TABLE A

[Greywacke]

hi all,me again... on mysql, i need to update records in table b to the values in table a - some regions were dropped from table c and are thus nonexistant causing many records to go unnoticed.i need to set the regions to the new regions now, but i am not completely sure of how the sql statement would look.sofar i have got:

UPDATE 	10_serviceprospects SET 	10_serviceprospects.bigint_RegionID 	= 	6_serviceleads.bigint_RegionID WHERE 	10_serviceprospects.text_Consumer 	= 	6_serviceleads.text_Consumer;

i can manage to select the records with deprecated region id's simply by doing a query as follows:

SELECT 	* FROM 	10_serviceprospects WHERE NOT EXISTS (	SELECT 		* 	FROM 		1_regions 	WHERE 1_regions.bigint_RegionID = 10_serviceprospects.bigint_RegionID);

is it possible to merge these statements, so that the records which have valid id's are not even touched?something like follows:

UPDATE 	10_serviceprospects SET 	10_serviceprospects.bigint_RegionID 	= 	6_serviceleads.bigint_RegionID WHERE NOT EXISTS (	SELECT 		* 	FROM 		1_regions 	WHERE 1_regions.bigint_RegionID = 10_serviceprospects.bigint_RegionID) AND 	10_serviceprospects.text_Consumer 	= 	6_serviceleads.text_Consumer;

just wish to hear any comments - "negative" or "positive" (i don't live in a world of duality so i take comments as comments )...

[Greywacke]

okay, tested and PMA responded as follows:

MySQL said: Documentation#1109 - Unknown table '6_serviceleads' in where clause

let me google on updating mysql from a second table...

[Greywacke]

and i came up with this page, describing the different ways to do a CROSS TABLE UPDATE.let me go read solong ^^

[Greywacke]

okay, from what i read - i can update it as follows:

UPDATE 	10_serviceprospects, 	6_serviceleadsSET 	10_serviceprospects.bigint_RegionID 	= 	6_serviceleads.bigint_RegionID WHERE NOT EXISTS (	SELECT 		* 	FROM 		1_regions 	WHERE 	1_regions.bigint_RegionID 	= 	10_serviceprospects.bigint_RegionID) AND 	10_serviceprospects.text_Consumer 	= 	6_serviceleads.text_Consumer;

and 78 records' region id's were updated ^^this issue has now been resolved