mysql update with join and count

i have got a problem with thi开发者_StackOverflows query.

update table1 as a
left join  (
select count(*) as 'rec' from table2 group by isin having rec<100 )
table2 as h on a.isin=h.isin 
set a.nohist='1' 

i would like to count rows per group, ask if they the count is smaller than 100 an set a flag in table 1.

i get alway an error:

1064 - 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 'as h on a.isin=h.isin set a.nohist='1'' at line 4

could you help me?

thx br

You are setting the alias for the subselect result to be table2 and then you "realias" that table2 to h. On top of that you try to join table h and a by isin.

You need to select isin inside the subselect if you want to be able to join against that table with the other two tables.

update table1 as a
left join  (
select count(*) as 'rec' from table2 group by isin having rec<100 )
-- here you have that error

check this doc http://dev.mysql.com/doc/refman/5.0/en/join.html

But, why do you want to perform a join if you're updating table1? What do you want to do?

But, why do you want to perform a join if you're updating table1? What do you want to do?

i would to set a flag on table1 (a.nohist) where in table2 are less than then 100 rows per isin (key in both tables).

is there a reason without a join?

suunto