0

Consider these two tables

TABLE A
id name surname
1  test  test2
2  test  test2

Table B
id name surname count
1  test test2

What I want to do is, for a given id in Table B, I will count the number of rows that have same name and surname combination from Table A, and update count column of Table B

I have tried this

UPDATE Table_A, TABLE_B
SET TABLE_B.count=COUNT(TABLE_A.id)
WHERE TABLE_A.name=TABLE_B.name AND TABLE_A.surname=TABLE_B.surname
AND TABLE_B.id=1

But I am getting Invalid use of group function error.

What is the correct syntax to achieve this?

My question is different from Update row with data from another row in the same table because that question doesn't address counting rows of the other table. Moreover, that question doesn't use two different tables.

Community
  • 1
  • 1
yasar
  • 13,158
  • 28
  • 95
  • 160
  • Possible duplicate of [Update row with data from another row in the same table](http://stackoverflow.com/questions/5574434/update-row-with-data-from-another-row-in-the-same-table) – Josh Rumbut Jun 17 '16 at 21:49
  • You would probably be better to calculate the aggregate separately, store it in a variable, then update with that variable. – Phrancis Jun 17 '16 at 21:49
  • I showed an answer for that sort of thing [here](http://stackoverflow.com/a/32670593) – Drew Jun 17 '16 at 21:55
  • Possible duplicate of [Insert value into specific column from another table with groupby](http://stackoverflow.com/questions/32670306/insert-value-into-specific-column-from-another-table-with-groupby) – Philipp Jun 18 '16 at 08:26

0 Answers0