SQL INSERT WITH JOIN

Question

I have a problem with a mysql query.

These are my 2 tables:

player_locations:

ID |  playerid  | type | location
---|-----------------------

and users:

ID  | playername | [..]
----|--------------------
 1  | example1   | ...

I want to insert into player_locations following:

ID |  playerid  | type | location
---|-----------------------
 1 |     1      |  5   |  DOWNTOWN

And thats my query:

INSERT INTO player_locations (id, type, location)
  SELECT u1.ID as playerid,
        d.type,
        d2.location
  FROM users u1
  INNER JOIN users u2
    ON 1 = 1
  INNER JOIN (SELECT 5 as type
        FROM DUAL) d
  INNER JOIN (SELECT "DOWNTOWN" as location
        FROM DUAL) d2
    ON 1 = 1
  WHERE u1.playername = "example1";

But when i have 6 rows in users it inserts 6 same rows in player_locations

Answer 1

Why not just write this?

INSERT INTO player_locations(id, type, location)
  SELECT u.ID as playerid, 5 as type, 'DOWNTOWN' as location
  FROM users u
  WHERE u.playername = 'example1';

The self join doesn't make any sense. You are not using any information from u2 in your query, so it just multiplies the number of rows. The extra joins for constants are unnecessary.

Answer 2

Take the WHERE clause out, put your select inside the () on the insert and that should do it for you.