Q:

Cross table update query in MySql

I can execute this kind of query in SQL Server:
update tblmembers 
set hits_archived = 
  (select count(*) 
   from tblhits 
   where tblhits.target_id=tblmembers.id 
   and tblhits.hitdate < '2009-11-01') 
where tblmembers.regdate < '2007-09-28'

How can I do this in MySql 4.0.23?
I tried to execute the query, but it returned this error message:
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 count(*) from tblhits where tblhits.target_id=tblmem

A:

This can also be done with a subquery using MySQL. 
Subquery Syntax

