I want to do something like this:
UPDATE tableA SET Afield1 =(SELECT COUNT(Bfield1) FROM tableB WHERE NOT Bfield2 GROUP BY Bfield3)
But 2 problems:
1) Its returning an error telling that I have 2 returning rows of subquery, and
2) I see a problem because where is the join? It should update Afield1 for each Arecord based on a count in Btable. There have to be a connection between the 2 tables via the primary & foreign key IDs in each table.
How should my MySQL update statement look in order to work?