Menu
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.
328
You can use a select-statement within an INSERT statement to insert zero, one, or more rows into a table from the result table of the select-statement. The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.
The MySQL INSERT INTO SELECT Statement The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables matches. Note: The existing records in the target table are unaffected.
We can perform MySQL UPSERT operation mainly in three ways, which are as follows: UPSERT using INSERT IGNORE. UPSERT using REPLACE. UPSERT using INSERT ON DUPLICATE KEY UPDATE.
To create an Insert Results queryFrom the Query Designer menu, point to Change Type, and then click Insert Results. In the Choose Target Table for Insert Results Dialog Box, select the table to copy rows to (the destination table).
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; According to this page you're close. But put all the values into your select. Something like:
insert into scores (score, difficulty, playerid ) select TheScoreThatImProviding, TheDifficultyThatImProviding, player.id from player where uniqueDeviceId = TheUniqueIdThatImProviding If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
