CODEWITHSUNDEEP
mysqlsqldatabaseinsert
GotoFinal
GotoFinal

Reputation: 3685

MySQL SELECT or INSERT if row not exist yet

I have 3 simple tables:
players:
CREATE TABLE IF NOT EXISTS players (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), UNIQUE KEY (name));

arenas:
CREATE TABLE IF NOT EXISTS arenas (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40), UNIQUE KEY (name));

and points:
CREATE TABLE IF NOT EXISTS points (playerID INTEGER, arenaID INTEGER, points INTEGER, PRIMARY KEY (playerID, arenaID));

I want insert new row to points table, and automatically insert rows to players and arenas if needed, currently I have just simple INSERT: 

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE ?),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE ?),
    ?
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

? will be replaced with value.
Hot to edit this SQL, so if there is no player (SELECT players.id FROM players WHERE players.name LIKE ?) then it will INSERT new player to this table, this same with arenas.
Like: (my pseudo-code)

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE "Mary" ON KEY NOT EXIST INSERT INTO players (name) VALUES ("Mary")),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE "BigBoy" ON KEY NOT EXIST INSERT INTO arenas (name) VALUES ("BigBoy")),
    200
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

And still insert that new points row.

Upvotes: 2

Views: 8189

Answers (2)

Nailgun
Nailgun

Reputation: 4179

You can use INSERT IGNORE statement.

INSERT IGNORE INTO players (name) VALUES (?);

INSERT IGNORE INTO arenas (name) VALUES (?);

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE ?),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE ?),
    ?
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

Upvotes: 4

Mahmoud Fawzy
Mahmoud Fawzy

Reputation: 24

Why don't do it in three steps, you can use a stored procedure

IF NOT EXISTS arenas (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(40), UNIQUE KEY (name))
begin
    INSERT INTO arenas (name) VALUES ("BigBoy")
end
IF NOT EXISTS players (id INTEGER PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20), UNIQUE KEY (name))
begin
    INSERT INTO players (name) VALUES ("Mary")
end

INSERT INTO points (playerID, arenaID, points) 
VALUES 
(
    (SELECT players.id FROM players WHERE players.name LIKE "Mary"),
    (SELECT arenas.id FROM arenas WHERE arenas.name LIKE "BigBoy"),
    200
) ON DUPLICATE KEY UPDATE points.points = LEAST(points.points, VALUES(points.points));

Upvotes: 0

Related Questions