merge two tables with different columns mysql

Question

I want to merge two tables with different columns mysql. For e.g.

Table 1 :

-------------------------------------------------------------
item_id   title   slug   type   views   updatedAt   createdAt
-------------------------------------------------------------
 1    sometitle someslg  1     43454    timestamp   timestamp
 2    sometitle someslg  1     43566    timestamp   timestamp

Table 2:

-------------------------------------------------------------
id    ptitle   slug     pviews   updatedAt   createdAt
-------------------------------------------------------------
 1  sometitle  someslg  3434    timestamp   timestamp
 2  sometitle  someslg  6454    timestamp   timestamp
 3  sometitle  someslg  5454    timestamp   timestamp

The above tables are examples. I have merged with UNION. Here is my query

SELECT * ((SELECT t1.item_id,t1.title,t1.slug,t1.type,t1.views,t1.updatedAt,t1.createdAt 
FROM table1
t1) UNION ALL (SELECT t2.id,t2.ptitle,t2.slug,'',t2.pviews,t2.updatedAt,t2.createdAt)) t3 
ORDER BY t3.item_id ASC

This is working fine After Executing the query i will get the results like

-------------------------------------------------------------
item_id   title   slug   type   views   updatedAt   createdAt
-------------------------------------------------------------
 1    sometitle someslg  1     43454    timestamp   timestamp
 1    sometitle someslg        3434     timestamp   timestamp
 2    sometitle someslg  1     43566    timestamp   timestamp
 2    sometitle someslg        6454     timestamp   timestamp
 3    sometitle someslg        5454     timestamp   timestamp

But i want a virtual column in the result that shows the row from which table as shown below

--------------------------------------------------------------------------
item_id   title   slug   type   views   updatedAt   createdAt   from_tbl
--------------------------------------------------------------------------
 1    sometitle someslg  1     43454    timestamp   timestamp   t1
 1    sometitle someslg        3434     timestamp   timestamp   t2
 2    sometitle someslg  1     43566    timestamp   timestamp   t1
 2    sometitle someslg        6454     timestamp   timestamp   t2
 3    sometitle someslg        5454     timestamp   timestamp   t2

MySQL or MS SQL Server? Don't tag products not involved...

jarlh
– jarlh

2016-02-09 12:49:31 +00:00
Commented Feb 9, 2016 at 12:49 — jarlh
– jarlh, Commented Feb 9, 2016 at 12:49

Gordon Linoff · Accepted Answer · 2016-02-09 12:49:41Z

10

Just add the column in:

SELECT t3.*
FROM ((SELECT t1.item_id, t1.title, t1.slug, t1.type, t1.views, t1.updatedAt, t1.createdAt, 't1' as from_tbl
       FROM table1 t1
      ) UNION ALL
      (SELECT t2.id, t2.ptitle, t2.slug, '', t2.pviews, t2.updatedAt, t2.createdAt, 't2'
      )
     ) t3
ORDER BY t3.item_id ASC

answered Feb 9, 2016 at 12:49

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Manoj K Over a year ago

I want "1" instead of 't1' and "2" instead of 't2' in result. show i can define this.

Gordon Linoff Over a year ago

Just use '1' instead of 't1' in the query.

Mukesh Kalgude · Accepted Answer · 2016-02-09 13:12:38Z

1

try this way

SELECT t3.*
FROM ((SELECT t1.item_id, t1.title, t1.slug, t1.type, t1.views, t1.updatedAt, t1.createdAt, '1' as from_tbl
       FROM table1 t1
      ) UNION ALL
      (SELECT t2.id, t2.ptitle, t2.slug, '', t2.pviews, t2.updatedAt, t2.createdAt, '2'  FROM table2 t2
      )
     ) t3
ORDER BY t3.item_id ASC

edited Feb 9, 2016 at 13:12

Mukesh Kalgude

4,8542 gold badges20 silver badges32 bronze badges

answered Feb 9, 2016 at 13:00

Pramod

967 bronze badges

Comments

Attapol T. · Accepted Answer · 2019-10-27 07:26:03Z

0

CREATE TABLE table3 AS ( select t1.*,t2.* from table1 as t1, table2 as t2 where t1.LinkID1 = t2.LinkID2 ); INSERT INTO table3

** LinkID1 and LinkID2 are index referance each together

edited Oct 27, 2019 at 7:26

answered Oct 27, 2019 at 7:20

Attapol T.

11 bronze badge

Comments

Hashim Aziz · Accepted Answer · 2023-04-20 04:02:39Z

0

This SQL script can achieve the same purpose without carrying out the headache of specifying the whole list of column names. This script can guess the match column names and remove duplicates on insertion process.

SET @source_table = '`users_old`.`url`';
SET @target_table = '`users_new`.`url`';

SELECT GROUP_CONCAT(column_name)
 FROM INFORMATION_SCHEMA.COLUMNS a 
 WHERE a.TABLE_NAME = 'url'
 AND a.TABLE_SCHEMA = 'users_old'
 AND a.COLUMN_NAME IN (
   SELECT b.COLUMN_NAME
   FROM INFORMATION_SCHEMA.COLUMNS b 
   WHERE b.TABLE_NAME = 'url'
   AND b.TABLE_SCHEMA = 'users_new' 
 ) INTO @columns;

SET @s = CONCAT('REPLACE INTO ', @target_table , ' (', @columns ,') ' , ' SELECT ', @columns ,' FROM ', @target_table ,' UNION ALL SELECT ', @columns , ' FROM ', @source_table);

PREPARE stmt FROM @s;
EXECUTE stmt;

edited Apr 20, 2023 at 4:02

Hashim Aziz

6,5777 gold badges63 silver badges102 bronze badges

answered Apr 20, 2022 at 13:04

Nady Shalaby

6347 silver badges7 bronze badges

1 Comment

Hashim Aziz Over a year ago

I made some edits to help make your code clearer and easier to understand for anyone approaching it with their own tables. Feel free to rollback if I've misunderstood it.

Collectives™ on Stack Overflow

merge two tables with different columns mysql

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related