CODEWITHSUNDEEP
mysqljoininsertrows
magl1te
magl1te

Reputation: 119

SQL INSERT WITH JOIN

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

Upvotes: 2

Views: 107

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 2

user2975403
user2975403

Reputation: 41

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

Upvotes: 0

Related Questions