Hello All,
I'm new to Mysql and am having some trouble. I'm trying to insert into a junction table, "action_x_recipient", and I'm getting a 2013 error code.
When I run the query w/o the insert, it runs fine.
Basically we get a big data dump (over 8 million rows) which I need to move into the correct tables before dropping the table,"SP". I've already added all distinct recipients (based on unique email) to the "Recipient" table and all unique actions to the "Action" table. There are more tables and junction tables, of course. I haven't had this problem with those tables, however there is more data considered in this situation.
Here are the tables in the query (I've removed fields which are irrelevant to this formula. Also please note that the SP.Mailingname_Id is not equal to MailingName.Id, rather it is represented by MailingName.SPMailing_Id):
Action_x_Recipient
'A_x_R_Id', 'int(11)', NULL, 'NO', 'PRI', NULL, 'auto_increment', 'select,insert,update,references', ''
'Action_Id', 'int(11)', NULL, 'YES', 'MUL', NULL, '', 'select,insert,update,references', ''
'Recipient_Id', 'int(11)', NULL, 'YES', 'MUL', NULL, '', 'select,insert,update,references', ''
Action
'Action_Id', 'int(11)', NULL, 'NO', 'PRI', NULL, 'auto_increment', 'select,insert,update,references', ''
'MailingName_Id', 'int(11)', NULL, 'YES', 'MUL', NULL, '', 'select,insert,update,references', ''
Recipient
'Recipient_Id', 'int(11)', NULL, 'NO', 'PRI', NULL, 'auto_increment', 'select,insert,update,references', ''
'Email', 'varchar(45)', 'latin1_swedish_ci', 'NO', 'UNI', NULL, '', 'select,insert,update,references', ''
MailingName
'MailingName_Id', 'int(11)', NULL, 'NO', 'PRI', NULL, 'auto_increment', 'select,insert,update,references', ''
'SPMailing_Id', 'int(11)', NULL, 'YES', 'UNI', NULL, '', 'select,insert,update,references', ''
SP
'SP_Id', 'int(11)', NULL, 'NO', 'PRI', NULL, 'auto_increment', 'select,insert,update,references', ''
'Mailing_Id', 'int(11)', NULL, 'YES', 'MUL', NULL, '', 'select,insert,update,references', ''
'SP_Report_Id', 'varchar(45)', 'latin1_swedish_ci', 'YES', 'MUL', NULL, '', 'select,insert,update,references', ''
'Email', 'varchar(145)', 'latin1_swedish_ci', 'YES', 'MUL', NULL, '', 'select,insert,update,references', ''
'Event_Type', 'varchar(45)', 'latin1_swedish_ci', 'YES', '', NULL, '', 'select,insert,update,references', ''
Here is the query:
INSERT IGNORE INTO action_x_recipient (Recipient_Id,Action_Id)
SELECT
recipient.Recipient_Id,
action.Action_Id
from
recipient
join
SP ON recipient.email=SP.email
join
mailingname ON SP.Mailing_Id=mailingname.SPMailing_Id
join
action ON mailingname.MailingName_Id = action.MailingName_Id
WHERE
SP.Event_Type='Sent' AND
(recipient.Recipient_Id,action.Action_Id) NOT IN (SELECT
Recipient_Id, Action_Id
FROM
action_x_recipient );
