1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

[SQL] MYSQL Migration Insert to Select error where it complains of duplicates where there is...

Discussão em 'Outras Linguagens' iniciado por Stack, Setembro 14, 2021.

  1. Stack

    Stack Membro Participativo

    I have a table called EventFeedbacks that I created using this:

    CREATE TABLE `EventFeedbacks` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `eventId` int unsigned NOT NULL,
    `subscriberId` int unsigned NOT NULL,
    `rating` int NOT NULL,
    `comments` text,
    PRIMARY KEY (`id`),
    UNIQUE KEY `EventFeedbacks_eventId_subscriberId` (`eventId`, `subscriberId`),
    KEY `eventId` (`eventId`),
    KEY `subscriberId` (`subscriberId`),
    CONSTRAINT `EventFeedbacks_ibfk_1` FOREIGN KEY (`eventId`) REFERENCES `Events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `EventFeedbacks_ibfk_2` FOREIGN KEY (`subscriberId`) REFERENCES `Subscribers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    );


    Inside this table, I have data that looks like this:

    id eventId subscriberId rating comments
    1 4 45 5
    2 8 70 4 Great!
    3 8 73 5 awesome
    4 8 71 5
    5 8 72 5 This was awesome!
    6 8 63 5 it was awesome... chef Peggy was a good host and teacher. the dish came out delicious
    7 13 132 5 Excellent
    8 13 135 4 Just kept kicking me out and having me rejoin but great overall!
    9 14 149 5 Great recipe!!
    10 16 173 5
    11 16 172 5 Great demo!! Pleasantly enjoyed.
    12 15 155 5 Amazing!
    13 17 201 5 awesome
    14 17 225 5 Good experience and delicious food
    15 17 219 3 Very slow to upload and show all the participants. It might of been my internet...
    16 17 220 5 When switching from silent mode to active mode there was a delay which caused me to wait a few seconds before I could speak.↵↵Loved the experience, I look forward to joining more classes!
    17 17 221 5 This was educational, fun, and also easy to follow all at once, and I had a blast. The only thing I would want to see implemented is the ability to use this app on a tablet or mobile device. The interface was clean and easy to navigate, and I really liked the ability to mark where I was in the steps of the recipe.
    18 18 251 5
    19 19 267 5
    20 19 269 5 The food was delicious and completely worth the minimal effort required! ↵↵It was a bit stressful keeping up with the chef. For some reason, my mic wasn't working so I couldn't ask questions. It would have also been a bit helpful if there were pictures or examples in the ingredients list that was provided beforehand. The ingredients for this included "wild mushrooms" and I wasn't sure which wild mushrooms to purchase. I ended up buying two kinds that were similar to the ones in the email thumbnail. Turned out amazing anyway.
    21 20 279 5 It was great! And my girls had a wonderful time. Things I think would make it easier is if the host chef was always able to pinned and for the recipe instructions to be included ahead of time. I had the ingredients but with kids its better to have a sense of the process before they start getting their hands in the mix. ↵Great afternoon, and I loved the step instructions on the side. That was a wonderful feature!
    22 20 278 5 The experience was awesome for me and my mom, we both enjoyed cooking with you guys.[the food was delicious]Talia & Natalia
    23 21 295 5 great
    24 21 288 4 As per my convo with help, the link did not get sent to my AOL; your help desk said it was a problem with their server.
    25 21 290 4 A couple of technical difficulties joining. Other than that it was great! I like the layout of the screen. The video quality and sound quality were great!
    26 24 332 5
    27 37 379 4 The App functioned great! I love the ability to interact with the instructor. However, in this particular case, I felt the instructing chef went way too fast - I often felt like I was halfway through the current step by the time she was moving onto the next one.

    Now what I want to do is migrate this table to a new table called Feedback, which I created like so:

    CREATE TABLE `Feedback` (
    `id` int unsigned NOT NULL AUTO_INCREMENT,
    `eventId` int unsigned NOT NULL,
    `participantId` int unsigned NOT NULL,
    `response` JSON NOT NULL,
    `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deletedAt` timestamp DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `Feedback_eventId_index` (`eventId`),
    KEY `Feedback_participantId_index` (`participantId`),
    CONSTRAINT `FeedbackEvent_ibfk_1` FOREIGN KEY (`eventId`) REFERENCES `Events` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `FeedbackParticipant_ibfk_1` FOREIGN KEY (`participantId`) REFERENCES `EventParticipants` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
    );


    and now I'm using this INSERT to migrate the data over:

    INSERT into Feedback (
    SELECT EF.id as id,
    EF.eventId as eventId,
    EP.id as participantId,
    CONCAT(
    '{ "rating": ',
    EF.rating,
    ', "comments": "',
    EF.comments,
    '" }'
    ) as response,
    NOW() as createdAt,
    NOW() as updatedAt,
    NULL as deletedAt
    from EventFeedbacks EF
    JOIN EventParticipants EP ON EF.subscriberId = EP.subscriberId
    );


    However, I am getting a strange error:

    Error: ER_DUP_ENTRY: Duplicate entry '13' for key 'Feedback.PRIMARY' Error: ER_DUP_ENTRY: Duplicate entry '13' for key 'Feedback.PRIMARY'


    To me, this make no sense as the ID's for the first table are all unique. Is there something I'm missing and is there a different way I can achieve this without getting this strange dup error?

    Continue reading...

Compartilhe esta Página