views:

200

answers:

1

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
+1  A: 

This can also be done with a subquery using MySQL.

Subquery Syntax

Ben Fransen
can you give me a simple example of how to do cross table update with Subquery syntax?
Anwar Chandra
Hmm you're query looks fine to me. What version of MySQL are you using?
Ben Fransen
it's MySql 4.0.23
Anwar Chandra
Then maybe the problem is described here: http://forums.mysql.com/read.php?10,10572,10572#msg-10572 Take a look at the fourth answer. It states that it at least works in vers ion 4.1.8.
Ben Fransen
seems I need to upgrade :( thx btw
Anwar Chandra