CODEWITHSUNDEEP
mysqlselectsql-updateinner-joininsert-into
user3247935
user3247935

Reputation: 23

Update a mysql table with another

I just want to copy only the values that don't match from table2 (tpl2) to tpl1. What is the right syntax to do it? I use "update inner join" but deletes values from tpl1. Should i use insert select instead? See bellow:

tpl1       tpl2             tpl1
------    -------          -------
col_1      col_1           col_1
1           1       --->     1
2           2                2
3           3                3
4           4                4
            5                5
            6                6
            7                7

Upvotes: 1

Views: 57

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You can do this with insert . . . select:

insert into tpl1(col1)
    select col_1
    from tpl2
    where not exists (select 1 from tpl1 where tpl1.col_1 = tpl2.col2);

If you want to prevent duplicates, you might also consider this approach. Create a unique index or constraint on tpl1(col_1):

create unique index tpl1(col_1);

Then simply ignore any values that already exist. This method of ignoring using on duplicate key update:

insert into tpl1(col_1)
    select col_1
    from tpl2
    on duplicate key update col_1 = tpl1.col_1;

The assignment does not change the table, so the result is that duplicates are ignored.

Upvotes: 1

Hunter McMillen
Hunter McMillen

Reputation: 61512

You can do this by left joining tpl1 to tpl2 and only inserting values that don't exist in tpl1 

insert into tpl1 (col_1)
   select a.col_1 
   from tpl2 a
      left join tpl1 b using (col_1)
   where 
      b.col_1 is null;

Upvotes: 0

Bill Karwin
Bill Karwin

Reputation: 562310

You may be able to use INSERT IGNORE. 

INSERT IGNORE INTO tpl1 SELECT * FROM tpl2;

This means that when the insert attempts to copy a given row from tpl2 to tpl1, and the row already exists (that is, the new row would conflict with an existing PRIMARY or UNIQUE KEY value), then it just skips that row.

Upvotes: 2

Related Questions