sql - Add / update column from a query SELECT? SQL

Question

I'm quite a novice on this and I don't know if I will explain myself well. I am trying to do an exercise in SQL in which asks me to update the data in an "X" table from other data in a "Y" table. The problem is that it is not about updating table X exactly like the data in table Y. I put the statement and my tables:

Update the "numJocs" field (number of games) for all platforms, depending on the number of games each of the platforms in the GAMES table has.

PLATFORM table:

where: "nom" is name.

GAMES table:

where: "nom" is name, "preu" is price, "idPlataforma" is idPlatform and "codiTenda" is storeCode, but only idPlataforma interested for this exercise.

If I do:

SELECT COUNT(games.idPlataforma)
FROM games
GROUP BY (games.idPlataforma)

I can see how many games there are for each platform. The result would be:

count(games.idPlataforma)
__________________________
2
1
2
2

I would like to be able to put this result in the PLATFORM table, column "numJocs". But I don't know how to do it ... I also don't want to put it manually, that is, a "2" in a row "1", etc ... but I would like to be able to make a query and add that query in the column that I have to fill in. He tried to do a thousand things, but nothing ... Any help?

Thanks!!

标签： sql

Answer 1

一次更新，您可以使用以下查询

update Product P
INNER JOIN (
SELECT games.idPlataforma, COUNT(games.idPlataforma) as cnt
FROM games
GROUP BY games.idPlataforma
) x ON P.id= x.idPlataforma
SET P.numJocs= x.cnt

下次每次进入新游戏时，您都会更新 numJocs