I would like to ask a question, or just show me the way.
There is a table called Questionnaire from it I get the records in a procedure and organize the data in it.
Here is my code:
CREATE DEFINER='myroot'@'%' PROCEDURE 'SP_OrganizaEnvioPesquisa'()
BEGIN
DECLARE no_more_rows BOOLEAN DEFAULT FALSE;
DECLARE registrantsIds INT DEFAULT 0;
DECLARE cur CURSOR FOR
SELECT RegistrantId FROM db_didyoubuy.Questionario GROUP BY RegistrantId;
DECLARE CONTINUE handler FOR NOT FOUND SET no_more_rows := TRUE;
OPEN cur;
questoes : LOOP
FETCH cur into registrantsIds;
IF no_more_rows THEN
leave questoes;
CLOSE cur;
END IF;
INSERT IGNORE INTO db_didyoubuy.Grupo (GrupoId, RegistrantIdReferencia) SELECT UUID(), registrantsIds;
COMMIT;
INSERT IGNORE INTO db_didyoubuy.QuestionarioENV (IdQuestionario, NomeDestinatario, EmailDestinatario, TelefoneDestinatario, GrupoId, DataVisita,
DataEnvio, DataResposta, RegistrantId, HeadingId, HeadingName, CompanyId, CompanyName, TipoVisita, TotalAdPoints, AWSMessageId)
SELECT
Id AS IdQuestionario,
NomeDestinatario,
EmailDestinatario,
TelefoneDestinatario,
(select GrupoId from db_didyoubuy.Grupo where RegistrantIdReferencia = registrantsIds) as Grupo,
DataVisita,
now() AS DataEnvio,
now() AS DataResposta,
RegistrantId,
HeadingId,
HeadingName,
CompanyId,
CompanyName,
TipoVisita,
TotalAdPoints,
UUID() AS AWSMessageId
FROM
db_didyoubuy.Questionario
WHERE RegistrantId = registrantsIds ORDER BY TotalAdPoints DESC LIMIT 5;
COMMIT;
END LOOP questoes;
CLOSE cur;
END;
After executing the procedure and sending a show warnings, it returns the following.
Ineedtodoaone-to-manyinsertion.InthiscaseaGUID(UUID)fromtheGrouptablefortheQuestionnaire.
WhatIdofirstisasearchofallmyusers(RegistrantId)intheQuestionnairetable,andforeachofthemIinsertintheGrouptablethenthatsameGuidinsertedintheGrouptableIrelatetothesameuser(RegistrantId).
ThefunnythingisthatwhenIgeneratethefirstinsertsinmyQuestionNeveltableitinsertsnormal,thesecondtimeIneedtorunthesameprocedure,itreturnsmetheerror.
"Error Code: 1452. Can not add or update child row: a foreign key constraint fails";
My base on sql is pretty basic, I'd like a light on how I could fix this procedure or how I could rewrite it.
Thank you in advance.