CODEWITHSUNDEEP
sqlmysql
Eric
Eric

Reputation: 138

merging two tables w/ same structure

Trying to merge two tables, but I can't really get my head around it. The structure is the exact same for both tables. However, every query I've researched seems to write one table over the other. I'm guessing this has to do with the fact that both tables share the exact same id.

As they share unique ids I'd like to have a new id assigned to the data inserted into table #2 from table #1.

CREATE TABLE `siteScoring` (
  `id` mediumint(9) NOT NULL auto_increment,
  `mid` mediumint(9) NOT NULL,
  `itemId` varchar(25) NOT NULL,
  `title` text NOT NULL,
  `topic` varchar(255) NOT NULL,
  `url` text NOT NULL,
  `votes` mediumint(10) NOT NULL,
  `comments` mediumint(6) NOT NULL,
  `user` varchar(25) NOT NULL,
  `itemTime` bigint(25) NOT NULL,
  `time` bigint(25) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7930 DEFAULT CHARSET=utf8

Upvotes: 0

Views: 2724

Answers (1)

David Andres
David Andres

Reputation: 31781

This query will insert into the siteScoring table all unique records of siteScoring1 and siteScoring2 (excluding their id columns, which will be automitcally assigned on insert):

INSERT INTO siteScoring(mid, itemid, title, topic, url,  
                        votes, comments, user, itemTime, time)
SELECT  mid, itemid, title, topic, url,  
        votes, comments, user, itemTime, time
FROM    siteScoring1

UNION

SELECT  mid, itemid, title, topic, url,  
        votes, comments, user, itemTime, time
FROM    siteScoring2

Upvotes: 3

Related Questions