Eric Eric - 2 months ago 5
MySQL Question

MySQL - Combining INSERT, VALUES, and SELECT?

I'm kind of new to SQL, and I'm really only interacting with it enough to get a high score database working. I'm using a php script to access my online MySQL db. Anyways.

What I have is 2 tables, Player and Score. Player has an id, uniqueDeviceId, and chosenname.
Score has the usual things youd expect.

What I'd really like to do in a single query (to reduce complexity...) is to combine the syntaxes of

INSERT INTO scores VALUES
and INSERT INTO scores SELECT...


Into some sort of monster, like

INSERT INTO scores(score,difficulty,playerid)
VALUES(TheScoreThatImProviding,TheDifficultyThatImProviding, (SELECT
id FROM player WHERE uniqueDeviceId = TheUniqueIdThatImProviding)
)


If that makes sense. I want to lookup the playerId (3rd "value"), and mix the result of that select with the input of the VALUES provided.

Is this possible? All the googling results ive found either have it all VALUES or all SELECT.

Alp Alp
Answer

Makes sense and is called INSERT SELECT. This is an example query for the uniqueDeviceId 123, Score 5 and Difficulty 'easy':

INSERT INTO scores (score, difficulty, playerid)
  SELECT 5, 'easy', id
  FROM player WHERE uniqueDeviceId = 123;
Comments