vendredi 1 juillet 2016

Update table from aggregate of another table

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.

Aucun commentaire:

Enregistrer un commentaire