CODEWITHSUNDEEP
mysqlmariadbbulkinserton-duplicate-keybulkupdate
user25485418
user25485418

Reputation: 1

How to retrieve ids of inserted or updated rows after "INSERT ON DUPLICATE UPDATE" MySQL?

I'm storing players of my game based on their level :

CREATE TABLE IF NOT EXISTS Player(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE,
    level TINYINT UNSIGNED NOT NULL,
    PRIMARY KEY(id)
);

I'm also tracking each player that is playing a given character :

CREATE TABLE IF NOT EXISTS PlayerCharacter(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    playerId INT UNSIGNED NOT NULL,
    characterId INT UNSIGNED NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(playerId) REFERENCES Player(id) ON DELETE CASCADE,
    FOREIGN KEY(characterId) REFERENCES Character(id) ON DELETE CASCADE,
    UNIQUE(playerId, characterId)
);

(In the following queries I'm using placeholders '?' because I'm using prepared queries from within NodeJS.)

I am writting the queries of a transaction that aim to refresh these two tables for a given characterId using bulked queries built from a large amount of data that consists of an array of [name, level].`There are no duplicate names in that array because all of these potentially new players are unique. I said "potentially new" because they can already be present in the table Player.

This is why I will be using INSERT INTO Player(name, level) VALUES (?), ..., (?) ON DUPLICATE KEY UPDATE level = VALUES(level); in order to either add the new player if it wasn't known, or update it with its new level if it was already known. The other players are left untouched.

Then I'm planning on partially clearing the table PlayerCharacter for the characterId DELETE FROM PlayerCharacter where characterId = ?.

My problem is that I need to know the inserted/updated ids of these rows in order to be able to insert new rows in the PlayerCharacter, but it seems to be less trivial that I thought.

I thought about using temporary tables but this feels overly complicated for something that seems so "simple" to me. Maybe there is a way to first insert in the PlayerCharacter by letting MySQL insert or update a row in the table Player and using that inserted or updated row's id as the playerId on the row it inserts in PlayerCharacter ? But I don't know if it's possible and again, seems very complicated to me. There has to be a way to somehow accumulate the ids after the first INSERT ON DUPLICATE UPDATE query.

Upvotes: 0

Views: 204

Answers (1)

danblack
danblack

Reputation: 14646

INSERT INTO Player(name, level) VALUES (?), ..., (?) ON DUPLICATE KEY UPDATE level = VALUES(level)
RETURNING id

Will return the ids of columns added/updated.

Then you can create a delete statement form this.

Unfortunately a current limitation is that the ids must be returned rather than forming a temporary table which would make the delete much easier.

ref: Manual for: INSERT RETURNING

Upvotes: 0

Related Questions