phpmyadmin Question posted in
The official documentation can be found here.

MySQL INSERT INTO inside UPDATE and SET new primary key as foreign key – Phpmyadmin

I have a table where some of the referenced datastores have been deleted and their foreign key reference nulled. I need to insert a fresh datastore and set the foreign key in the reference table.

All of the IDs are UUIDs and my pseudo query looks like this but does not work:

UPDATE `rcachievements_player_achievements`
SET data_id = (INSERT INTO `rcachievements_datastore` (data) VALUES ('{"count":0}'))
WHERE data_id is null

Is there any way to insert the datastore and then set the id in the data_id field for every row that has a missing data store?
table structure

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I ended up doing it with a FOR LOOP, like the following:

    DELIMITER //

FOR i IN (SELECT id FROM `rcachievements_player_achievements` WHERE data_id is NULL)
DO
        SET @id = UUID();
        INSERT INTO `rcachievements_datastore` (id, data, version, when_created, when_modified) VALUES (@id, '{"count":0}', 1, CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP());
        UPDATE `rcachievements_player_achievements` SET data_id = @id WHERE id = i.id;
END FOR;
//

DELIMITER ;

SELECT id FROM `rcachievements_player_achievements` WHERE data_id is NULL;

  2. 0

    You have to do it as multiple queries. First insert the new row into rcachievements_datastore, then update all the referencing rows.

    SET @id = UUID();
INSERT INTO `rcachievements_datastore` (id, data) VALUES (@id, '{"count":0}');

UPDATE `rcachievements_player_achievements`
SET data_id = @id
WHERE data_id is null;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search