Is there a fast way to update a column in one table with the column value in another table? Here's the catch. I need to do this for up to 10,000 rows. I would like to do this with one sql statement rather than writing a PHP loop to do it.
I have 2 tables, Dept and Employee. I want to count the # of employees in each dept and store it in the corresponding Dept row. To do the tally it is simply creating a temporary table: drop table if exists empl_count; create table empl_count select dept_id, count(*) num from employee group by dept_id; create index dept_ndx on empl_count (dept_id); Ok, now that I have the empl_count table, now what? I need an sql statement like: update Dept set dept.num_employees = empl_count.num where dept.dept_id=empl_count.dept_id; I can write a PHP program to loop through the dept table and lookup each empl_count.num for each dept_id, but this means doing an update for each Dept row. There could be 10,000 rows which means 10,000 individual sql updates. Yikes! The best idea I can come up with is to sort the empl_count table on num so I can update maybe a dozen or so Dept rows at a time with the same value. But even so, it is a bit of work and still means hundreds (or thousands) of updates. Are there any ideas on how I can speed this up? TIA Brent _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
