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