CODEWITHSUNDEEP
mysqlsql
user8966659
user8966659

Reputation:

Insert type id in table

How to insert score with value and score_id ? At server im know only score_type.name and score.value. How i can insert new score with score_type ? If score_type name exsist just get id and insert, else create, get id and insert.

tables

Upvotes: 0

Views: 89

Answers (2)

Barmar
Barmar

Reputation: 781058

First try to create the score_type if it doesn't exist:

INSERT IGNORE INTO score_type (name) VALUES ("type_name");

Then use INSERT ... SELECT to insert the ID into the score table:

INSERT INTO score (value, score_type_id, player_id)
SELECT 123, id, "player_name"
FROM score_type
WHERE name = "type_name";

The first INSERT assumes you have a unique index on score_type.name. IGNORE means to fail silently if you try to insert a duplicate name.

Replace 123 and player_name with the known score.value and score.player_id.

Upvotes: 1

maio290
maio290

Reputation: 6732

If I understood your question correctly, you want to fill the score_type_id dynamically by selecting it using the name:

INSERT INTO `score`(`value`, `score_type_id`, `player_id`) VALUES (1337, (SELECT id FROM score_type WHERE score_type.name = "test") ,"maio290");

The trick is just to use another query instead of a fixed value.

Upvotes: 0

Related Questions