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

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...