INSERT/REPLACE INTO...SELECT; duplicate problems

Question

Events from my database bd_yellowbox should be added to the database groupofficecom. The data of one event in bd_yellowbox is stored in three attributes tables element, actions, and actionsparam. All three tables have one column in common, ID_ELEMENT. The column has the same value in all three tables for one data point.

The data of one event in groupofficecom is stored in two tables, cal_events and cf_cal_events. The primary key of cal_events is id and is auto incremented. The primary key of cf_cal_events is model_id and is NOT auto incremented.

cf_cal_events.model_id = cal_events.id

The two databases are linked through a column in the table cf_cal_events:

cf_cal_events.col_10 = bd_yellowbox.element.ID_ELEMENT

Here's my original code:

REPLACE INTO groupofficecom.cal_events (data1,data2)
VALUES ('1','Tom Hanks')

REPLACE INTO groupofficecom.cf_cal_events (model_id, col_10)
SELECT groupofficecom.cal_events.id, bd_yellowbox.element.ID_ELEMENT
FROM bd_yellowbox.element, groupofficecom.cal_events
WHERE bd_yellowbox.element.ID_ELEMENT=bd_yellowbox.actions.ID_ELEMENT
AND bd_yellowbox.element.ID_ELEMENT=bd_yellowbox.actionsparam.ID_ELEMENT;

Problem #1: the insertion of data in cf_cal_events is resulting in one event being inserted as multiple events with different IDs.

Problem #2: when I re-execute the query, the events are re-inserted into both tables as new events. This creates new ids because of the AUTO_INCREMENT of cf_events.id, which prevents changed events from being updated in the bd_yellowbox database.

THANK YOU ALL!

Answer 1

Problem #0: You still haven't provided the https://stackoverflow.com/help/mcve. And are you sure that your current code snippet will even execute?

Problem #1

I think that this problem has to do with the structure of your database. As I suppose:

One yellow_box.element can have multiple yellow_box.action - it is logical that one element can be acted upon many times.

One yellow_box.action can have multiple yellow_box.action_param - it is less obvious and may not be so

It means that if we have:

CREATE TABLE yellow_box.element
(
    ElementId INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Description nvachar(128)
)

CREATE TABLE yellow_box.actions
(
    ActionId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ElementId INT NOT NULL REFERENCES yellow_box.element(ElementId),
    Description nvachar(128)
)

CREATE TABLE yellow_box.actions_param
(
    ActionParamId INT NOT NULL AUTO_INCREMENT PRIMARY KEY
    ActionId INT NOT NULL REFERENCES yellow_box.actions(ActionId),
    Description nvachar(128)
)

With 1 element, two actions on this element, each with two actions_param, how many rows will be returned by

SELECT element.ElementId, action.ActionId, param.ActionParamId 
FROM yellow_box.element AS element
    INNER JOIN yellow_box.actions AS action
    ON action.ElementId = element.ElementId
    INNER JOIN yellow_box.actions_param AS param
    ON param.ActionId = action.ActionId

It will be 4 rows.

That's from where all those duplicate rows come - your joined(just using the WHERE clause) those tables increasing the result set, but never used any columns form them - that's why you've seen complete duplicates:

SELECT "I am the duplicate" 
FROM yellow_box.element AS element
    INNER JOIN yellow_box.actions AS action
    ON action.ElementId = element.ElementId
    INNER JOIN yellow_box.actions_param AS param
    ON param.ActionId = action.ActionId

Will return 4 rows with "I am the duplicate"

Problem #2

REPLACE INTO doesn't go well with auto incrementing primary keys MySQL REPLACE in an auto incremented row, PHP mySQL - Insert new record into table with auto-increment on primary key.

You could try the DEFAULT placeholder on primary key column on replace:

REPLACE INTO yellow_box.actions_param(ActionId, Description)
VALUES (100, "Original")

REPLACE INTO yellow_box.actions_param yellow_box.actions_param(ActionParamId, ActionId, Description)
VALUES (DEFAULT, 100, "Original")

But I am not sure that it will work

or you could try to use the update statement http://www.tutorialspoint.com/mysql/mysql-update-query.htm to directly update the rows using their primary key value to identify them.