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

What does your question have to do with java (tag removed)? — Pshemo
– Pshemo, Commented Dec 28, 2014 at 19:23

Gordon Linoff · Accepted Answer · 2014-12-28 19:43:28Z

2

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.

answered Dec 28, 2014 at 19:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user2975403 · Accepted Answer · 2014-12-28 19:22:04Z

0

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

answered Dec 28, 2014 at 19:22

user2975403

418 bronze badges

2 Comments

magl1te Over a year ago

I need the WHERE clause because I have to get the ID of the playername in the WHERE clause

user2975403 Over a year ago

Isnt the ID being consumed at the start of the select statement? If you want it to get the id from all the rows under [user] you will have to remove the where, if you don't then it will fill [player_location] with the single result the select returns.

Collectives™ on Stack Overflow

SQL INSERT WITH JOIN

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related