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, return 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