want to update table with data from pseudo join

[sneakyimp]

i have a table with some 45,000 records in it. i'm having problems with a query running slow and i'd like to update this table with a code that i can easily get when i run this query:

SELECT
  ic.ind_cat_code,
  ndl.*
FROM
 myplan_nat4d_dl2 ndl,
 myplan_industry_categories ic,
 myplan_industry_category_naics_assoc ica
WHERE
 LEFT(ndl.naics,2)=ica.naics2
 AND ica.ind_cat_code = ic.ind_cat_code

basically, i want to update the ndl table with the ind_cat_code value from the ic table. The problem i have is that i can only get to the value i need by way of an association table that connects each record in my table of interest to the particular value of interest.

i tried this query but it didn't work:

UPDATE myplan_nat4d_dl2
SET myplan_nat4d_dl2.ind_cat_code= myplan_industry_categories.ind_cat_code
WHERE
 LEFT(myplan_nat4d_dl2.naics,2) = myplan_industry_category_naics_assoc.naics2 AND
 myplan_industry_category_naics_assoc.ind_cat_code = myplan_industry_categories.ind_cat_code

it complains about unknown table in the where clause. if i put it in the UPDATE clause, i get mysql syntax error. can anyone suggest a query whereby i can get values from this pseudo/quasi JOIN into my 1st table?

AND...for what it's worth, this is the query that's really slow. the slowness seems to come because of the GROUP BY part...since the grouping specified is not an indexed field, it's pretty slow for 45,000 records. if i could get this done in under a second, then i could ignore the first problem.

SELECT
 ic.ind_cat_code,
 ic.title,
 COUNT(DISTINCT nld.occ_code)
FROM
 myplan_nat4d_dl nld,
 myplan_nat4d_dl nld2,
 myplan_national_dl nat,
 myplan_industry_categories ic,
 myplan_industry_category_naics_assoc ica
WHERE
 nld2.occ_code='00-0000' AND
 nld.occ_code != '00-0000' AND
 nld.naics=nld2.naics AND
 nld.occ_code=nat.occ_code AND
 nat.occ_code != '00-0000' AND
 ((nld.tot_emp/nld2.tot_emp)/(nat.tot_emp/127567910)) > 0.2 AND
 LEFT(nld.naics,2) = ica.naics2 AND
 ica.ind_cat_code = ic.ind_cat_code
 GROUP BY ic.ind_cat_code
 ORDER BY ic.title

[sneakyimp]

apparently you can't do a multi-table UPDATE for mySQL before version 4.0.4. this also doesn't work:

UPDATE
 myplan_nat4d_dl2,
 myplan_industry_categories,
 myplan_industry_category_naics_assoc
SET
myplan_nat4d_dl2.ind_cat_code = myplan_industry_categories.ind_cat_code WHERE
 LEFT( myplan_nat4d_dl2.naics, 2 )=myplan_industry_category_naics_assoc.naics2
 AND myplan_industry_category_naics_assoc.ind_cat_code = myplan_industry_categories.ind_cat_code 

[Oersoep]

No, you can't do multiple talbe updates, but maybe this'll help:

REPLACE INTO table1 SELECT field1, field2, 'value3' FROM other_table;

I guess you can make a complex SELECT.

If you're using foreign key constraints with cascades you might want to be careful though. And you have to know value of the primary key of table1.
If it doesn't exactly match the primary key it won't overwrite the old row but create a new one.
And be careful: It doensn't update, it only DELETEs and INSERTs.

[sneakyimp]

thanks so much for the help. i ended up writing a php script to handle it. it was pretty simple.