How to add columns from one MySQL table to another without duplicates?-CodePudding

There are two tables. It is necessary to insert two fields from one to the other so that duplicates do not appear. I tried those methods that are described for

 USING INSERT IGNORE
 Using replace
 USING INSERT ... on duplicate key update

But I didn't succeed. For example, it ignore duplicate and write these:

REPLACE INTO user_favorites
(user_id, partner_id)
SELECT id, partner_id FROM users

How to do it?

1 table

create table local.users
(
    id                       int auto_increment,
    name                     varchar(255)             null,
    email                    varchar(255)             null,
    password                 varchar(255)             null,
    partner_id               int                      null,
    
    constraint users_email_unique
        unique (email),
    constraint users_id_uindex
        unique (id)
)
alter table local.users
    add primary key (id);

2 table

create table local.user_favorites
(
    id         int auto_increment,
    user_id    int       null,
    partner_id int       null,
    constraint user_favorites_id_uindex
        unique (id),
    constraint user_favorites_partners_id_fk
        foreign key (partner_id) references local.partners (id)
            on update cascade on delete cascade,
    constraint user_favorites_users_id_fk
        foreign key (user_id) references local.users (id)
            on update cascade on delete cascade
); 
alter table local.user_favorites
    add primary key (id);

CodePudding user response：

insert ignore and insert ... on duplicate key and replace all detect duplicates by whatever unique key constraints you have. Right now, your only unique constraint in user_favorites is the primary key id, which obviously doesn't help.

Add a unique constraint on user_id and partner_id:

alter table local.user_favorites add unique (user_id,partner_id);

If that fails, you already have duplicates that you will need to clean up first.

Then do any of the things you tried to add the rows from users.

CodePudding user response：

You can first add empty columns and then update corresponding values by join operation. Like this:

ALTER TABLE user_favorites ADD COLUMN 
    name  VARCHAR(255)  NULL,
    email  VARCHAR(255)  NULL,
    password  VARCHAR(255)  NULL;
    CONSTRAINT users_email_unique UNIQUE(email);
    
UPDATE user_favorites tb1
INNER JOIN users tb2 ON tb1.user_id = tb2.id
AND tb1.partner_id = tb2.partner_id
SET tb1.name = tb2.name
    tb1.email = tb2.email
    tb1.password = tb2.password;

Reference here: https://www.tutorialspoint.com/can-we-add-a-column-to-a-table-from-another-table-in-mysql