-1

I have two tables that look like this:

  • game (id, title, location)
  • gamePlayers (playerType, gameId)

I can see that I have 90 games that do not have a corresponding id in my gamePlayers table with this query:

SELECT * 
FROM dbo.game
WHERE id NOT IN (SELECT gameId FROM dbo.gamePlayers)

So I want to create entries for the missing games and add the value '1' for the playerType, and the id of the game for gameId.

So I tried this:

INSERT INTO dbo.gamePlayers
    SELECT 1, p.id 
    FROM dbo.game p
    WHERE p.id NOT IN (SELECT gameId FROM dbo.gamePlayers)

But it doesn't insert anything at all.

Shouldn't it insert 90 rows?

Thanks!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
SkyeBoniwell
  • 6,345
  • 12
  • 81
  • 185
  • 2
    Do you have any NULL gameID values? `NOT IN` will never return true in that case. Check with `SELECT gameId FROM dbo.gamePlayers WHERE gameId IS NULL;`. – Dan Guzman Dec 03 '21 at 14:39
  • 3
    Is `gameId` `NULL`able, by any chance? If so, this is [documented](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/in-transact-sql?view=sql-server-ver15#result-value) behaviour (see the big red box). Use a `NOT EXISTS` instead. To be honest, I often think using `(NOT) EXISTS` in the first place is often the better choice. – Thom A Dec 03 '21 at 14:39
  • @DanGuzman No I just checked. There are no NULL gameID values in the gamePlayers table. – SkyeBoniwell Dec 03 '21 at 14:41
  • @Larnu I have gameId set to NOT NULL. thank u – SkyeBoniwell Dec 03 '21 at 14:41
  • What does the `SELECT` in your `INSERT INTO` statement return, if you run it just by itself ? Does it in fact return 90 rows as you expect? – marc_s Dec 03 '21 at 14:42
  • 1
    @marc_s it does return 90 rows if I just run it without the INSERT INTO part – SkyeBoniwell Dec 03 '21 at 14:44
  • 4
    You should specify the column list for your `INSERT` statement to make sure you're inserting into the correct columns. – Richard Deeming Dec 03 '21 at 14:44
  • 1
    Considerng that, apart from the columns in the `SELECT` and the preceding `INSERT INTO` clause, those statements are the same, this suggests what is in your question, and what you are doing are not equal. Time for a [mre] in my opinion. – Thom A Dec 03 '21 at 14:45
  • Time for giving us the response that MS-SQL is giving you (I suspect something like "Column name or number of supplied values does not match table definition.") – Luuk Dec 03 '21 at 14:47
  • Does this answer your question? [NOT IN vs NOT EXISTS](https://stackoverflow.com/questions/173041/not-in-vs-not-exists) – Charlieface Dec 03 '21 at 15:10

1 Answers1

3

Does the following slight refactor work for you?

insert into dbo.gamePlayers(playerType, gameId)
select 1, p.id 
from dbo.game p
where not exists (select * from gamePlayers gp where gp.gameId=p.Id)
Stu
  • 30,392
  • 6
  • 14
  • 33