Replicate table in MySQL

Question

I don't have any idea about how to replicate table. But this is my problem, I have table_1 with 5 columns and have data on it, then I have table_2 with 10 columns and no data on it. All of column in table_1 is same in table_2 so which means only 5 column in table_2 are same in table_1, so i want to replicate the other 5 columns from table_2 to table_1 but it will not affect the data on table_1. How can I do that? Thank in advance.

table_1

table_2

expected output on table_1

Can you show us sample data for `table_1` and `table_2` so that this is clear? How are the two tables related? — Tim Biegeleisen, May 16 '16 at 02:01
Is this [mysql :: insert into table, data from another table](http://stackoverflow.com/questions/4241621/mysql-insert-into-table-data-from-another-table) what are you looking for ? — Ren Mark Almojera Cerdan, May 16 '16 at 02:02
@renmark, no. I think I need to copy some column from table_2 to table_1 — Dunhill Dimaapi, May 16 '16 at 02:09
@TimBiegeleisen, I have output because I don't know how to replicate column from table to another table. — Dunhill Dimaapi, May 16 '16 at 02:50
@DunhillDimaapi `replicate column from table to another table` ... what does this even mean if you can't show your expected output? — Tim Biegeleisen, May 16 '16 at 02:56
Ow. sorry. I understand now. So table_1 will have a same column as table_2 but not affecting the data. I will upload image of output. — Dunhill Dimaapi, May 16 '16 at 02:58
Is there any data from `table_2` which you wish to bring into `table_1`, or do you just want empty columns in `table_1` ? — Tim Biegeleisen, May 16 '16 at 03:09
there is not data on table_2. I want to copy the field from table_2 to table_1 but not affecting the data of table_1 — Dunhill Dimaapi, May 16 '16 at 03:11
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/112010/discussion-between-tim-biegeleisen-and-dunhill-dimaapi). — Tim Biegeleisen, May 16 '16 at 03:19

score 1 · Answer 1 · answered May 16 '16 at 02:40

1

Here is how you can replicate/copy over a table and all the information in it.

Select your table
Click on the "Operations" tab
Go to "Copy table to (database.table):
Select and fill in the appropriate information.

answered May 16 '16 at 02:40

Edward

2,291
2
19
33

I got this error. "Table table_1 is already exists". I want to copy the column from table_2 to table_1, I mean the col_5 to col_10. – Dunhill Dimaapi May 16 '16 at 02:49

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

You can use insert with ON DUPLICATE KEY UPDATE which will update existing rows. However this works based on a unique key constraint. Which means that you'll have to create the constraint or update an existing one.

It does require unique data exist across the 5 columns. So that when rows from table_2 are inserted and unique constraint would be violated it will update the row instead of complete an insert.

ALTER TABLE table_1
    ADD UNIQUE INDEX ix_unique (col_1, col_2, col_3, col_4, col_5);
    
INSERT INTO table_1(col_1, col_2, col_3, col_4, col_5,
                    col_6, col_7, col_8, col_9, col_10)
SELECT t.col_1, t.col_2, t.col_3, t.col_4, t.col_5,
       t.col_6, t.col_7, t.col_8, t.col_9, t.col_10
  FROM table_2 t
ON DUPLICATE KEY UPDATE col_6 = t.col_6, col_7 = t.col_7, col_8 = t.col_8, col_9 = t.col_9, col_10 = t.col_10;

INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

EDIT

I was able to reproduce this on mysql 5.7, and I've seen this functionality since 5.5.

Here's the script that I ran.

CREATE TABLE IF NOT EXISTS  `table_1` (
  `col_1` int(10) unsigned NULL,
  `col_2` int(10) unsigned NULL,
  `col_3` int(10) unsigned NULL,
  `col_4` int(10) unsigned NULL,
  `col_5` int(10) unsigned NULL,
  `col_6` int(10) unsigned NULL,
  `col_7` int(10) unsigned NULL,
  `col_8` int(10) unsigned NULL,
  `col_9` int(10) unsigned NULL,
  `col_10` int(10) unsigned NULL,
  UNIQUE INDEX ix_unique (col_1, col_2, col_3, col_4, col_5)
);


CREATE TABLE IF NOT EXISTS  `table_2` (
  `col_1` int(10) unsigned NULL,
  `col_2` int(10) unsigned NULL,
  `col_3` int(10) unsigned NULL,
  `col_4` int(10) unsigned NULL,
  `col_5` int(10) unsigned NULL,
  `col_6` int(10) unsigned NULL,
  `col_7` int(10) unsigned NULL,
  `col_8` int(10) unsigned NULL,
  `col_9` int(10) unsigned NULL,
  `col_10` int(10) unsigned NULL
);


INSERT INTO table_1 (`col_1`, `col_2`, `col_3`, `col_4`, `col_5`)
VALUES
  (1, 1, 1, 1, 1),
  (2, 2, 2, 2, 2),
  (3, 3, 3, 3, 3),
  (4, 4, 4, 4, 4),
  (5, 5, 5, 5, 5),
  (6, 6, 6, 6, 6),
  (7, 7, 7, 7, 7),
  (8, 8, 8, 8, 8),
  (9, 9, 9, 9, 9),
  (10, 10, 10, 10, 10);


INSERT INTO table_2 (`col_1`, `col_2`, `col_3`, `col_4`, `col_5`, `col_6`, `col_7`, `col_8`, `col_9`, `col_10`)
VALUES
  (1, 1, 1, 1, 1, 1, 1, 1, 1, 1),
  (2, 2, 2, 2, 2, 2, 2, 2, 2, 2),
  (3, 3, 3, 3, 3, 3, 3, 3, 3, 3),
  (4, 4, 4, 4, 4, 4, 4, 4, 4, 4),
  (5, 5, 5, 5, 5, 5, 5, 5, 5, 5),
  (6, 6, 6, 6, 6, 6, 6, 6, 6, 6),
  (7, 7, 7, 7, 7, 7, 7, 7, 7, 7),
  (8, 8, 8, 8, 8, 8, 8, 8, 8, 8),
  (9, 9, 9, 9, 9, 9, 9, 9, 9, 9),
  (10, 10, 10, 10, 10, 10, 10, 10, 10, 10);

INSERT INTO table_1(col_1, col_2, col_3, col_4, col_5,
                    col_6, col_7, col_8, col_9, col_10)
SELECT t.col_1, t.col_2, t.col_3, t.col_4, t.col_5,
       t.col_6, t.col_7, t.col_8, t.col_9, t.col_10
  FROM table_2 t
ON DUPLICATE KEY UPDATE col_6 = t.col_6, col_7 = t.col_7, col_8 = t.col_8, col_9 = t.col_9, col_10 = t.col_10;

when I tried your code, this is the output: Unrecognized keyword. (near "ON" at position 236) Unrecognized keyword. (near "DUPLICATE" at position 239) Unrecognized keyword. (near "KEY" at position 249) An alias was previously found. (near "t" at position 268) An alias was previously found. (near "col_6" at position 270) An alias was previously found. (near "t" at position 285) An alias was previously found. (near "col_7" at position 287) An alias was previously found. (near "t" at position 302)..... — Dunhill Dimaapi, May 16 '16 at 07:02
@DunhillDimaapi: I've added repro steps to see if that can help clarify. Not sure which version of mysql you are running, but this is mysql specific functionality and the question is tagged with mysql. — Saqib Rokadia, May 16 '16 at 17:23

Replicate table in MySQL

2 Answers2

EDIT