CODEWITHSUNDEEP
mysql
bAris
bAris

Reputation: 121

Combine INSERT INTO and SELECT on MySQL

Step 1) Find the ids in users table that does not exist in players table. Step 2) Insert into players table with those missing ids

So I should combine these two queries. (Giving the result of the SELECT query as a list to the INSERT INTO query)

SELECT users.uid 
FROM users 
WHERE users.uid NOT IN (select uid from players);

INSERT INTO players (uid)
VALUES ()

What is the cleanest way to do this?

Upvotes: 2

Views: 2658

Answers (3)

user3600910
user3600910

Reputation: 3109

Try this:

INSERT INTO users (uid)
(SELECT users.uid
   FROM users
   LEFT JOIN players ON users.uid = players.uid)
   WHERE players.uid IS null)

Upvotes: 1

Luthando Ntsekwa
Luthando Ntsekwa

Reputation: 4218

Place your select query below the insert query and remove values(): 

INSERT INTO players (uid)
SELECT users.uid 
FROM users 
WHERE users.uid NOT IN (select uid from players);

EDIT Based On Comments
An easy way would be to use INSERT IGNORE - This will basically insert all the uid that are not in the players's table and ignore the rest: 

INSERT IGNORE INTO players (uid)
SELECT users.uid 
FROM users

Upvotes: 3

Mxx persian
Mxx persian

Reputation: 105

INSERT INTO players 
SELECT users.uid 
FROM users 
WHERE users.uid NOT IN (select uid from players);

Upvotes: 0

Related Questions