How to initialise values in one table with a group by from another table in sql

Question

I need to use create a column in one table and set the values in the column as groups based on values in another table.

I keep getting this error:

ORA-01427: single-row subquery returns more than one row

And this one:

ORA-01400: cannot insert NULL into ("SRUB0001"."GAME"."GAME_ID")

Here is my code:

UPDATE team
SET
    no_players = (
        SELECT
            COUNT(player_id) AS no_players
        FROM game
        GROUP BY game_id
    );

Think very carefully about what you are asking your update to do. The value being assigned to each row for `no_players` is expected to be a *single scalar value*. Your sub query is returning one row per *each set* of grouped rows. Which single one of those many rows should be used? The query optimizer is saying "I dunno". Hint - correlation. — Stu, Oct 16 '22 at 10:16

score 0 · Answer 1 · answered Oct 16 '22 at 06:10

0

try over() clause to match no of columns in both tables

answered Oct 16 '22 at 06:10

Nadika Dushan

Getting this:SQL Error: ORA-00913: too many values – Simeon Rubin Oct 16 '22 at 06:22
add "team" and "game" table structures to the problem. without knowing the table structure it is hard to correct your code. – Nadika Dushan Oct 16 '22 at 08:42
As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 20 '22 at 01:38

1 Answers1