Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Merge several tables into one where tables have the same columns but only add rows where unique values exist on one column

The below code works to insert rows into table_main on an mySQL database where table_A contains unique values from the column primary_isbn13

INSERT INTO `table_main`
SELECT * FROM `table_A` A
WHERE NOT EXISTS (SELECT 1 FROM `table_main` X 
                  WHERE A.`primary_isbn13` = X.`primary_isbn13`)

I have several tables to merge into table_main where unique primary_isbn13 exist. Rather then keep running the same sql statement several times replacing table_A with table_B then table_C etc is there a way to SELECT several tables – table_A, table_B and table_C and merge them into table_main where unique primary_isbn13 values exist?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Needed to make primary_isbn13 unique aka primary key in table structure then run

    INSERT IGNORE INTO main_table
SELECT * FROM table_A;
INSERT IGNORE INTO main_table
SELECT * FROM table_B;
INSERT IGNORE INTO main_table
SELECT * FROM table_C;

  2. 0

    Use UNION:

    INSERT INTO table_main
SELECT * FROM table_A A
WHERE NOT EXISTS (SELECT 1 FROM table_main X WHERE A.primary_isbn13 = X.primary_isbn13)
UNION
SELECT * FROM table_B B
WHERE NOT EXISTS (SELECT 1 FROM table_main X WHERE B.primary_isbn13 = X.primary_isbn13)
UNION
SELECT * FROM table_C C
WHERE NOT EXISTS (SELECT 1 FROM table_main X WHERE C.primary_isbn13 = X.primary_isbn13)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search