How do I combine 2 tables in MySQL 4.0.17?

ppowell

I have db1.table1 and db2.table1.. bluntly put, how exactly do I create this:

db3.table1 = db1.table1 + db2.table1 (with unique rows!)

Sorry but I can't figure it out, UNION, INSERT INTO SELECT, CREATE TABLE SELECT, MERGE, everything fails with syntax errors, and I don't know what to do after 3 days of trying!

Please help someone!

Thanx
Phil

tomhath

This should be all you need (of course don't insert an identity column)

insert into db3.table1 (col1, col2)
select col1, col2 from db1.table1 where 1
union
select col1, col2 from db2.table1 where 1

ppowell

I tried that and got the following error:

Syntax error near 'union select..'

I can do "select union select", however, the moment I embed that within an INSERT or even CREATE TABLE it throws a syntax error every time

Phil

bretticus

Have you tried doing the INSERT ... SELECTs seperately and adding a ON DUPLICATE KEY UPDATE on the second query? Just a thought off the top of the head.

tomhath

bretticus is right, older versions of MySQL don't do UNION or subselects very well.

You'll need to use the LEFT JOIN trick in place of a NOT EXISTS in the second INSERT

See the explantion on subselects here:

http://www.informit.com/articles/article.asp?p=30875&seqNum=5&rl=1

ppowell

Good news!!! This works

DROP TABLE /! IF EXISTS / spring_summer;

CREATE TABLE /! IF NOT EXISTS / spring_summer SELECT FROM Spring.Students UNION SELECT FROM Summer.Students;

The bad news is that it causes multiple duplicate records (with unique IDs) to appear in the table, and these dups will display on the web front end since their IDs are unique. How do I tackle this?

Phil

tomhath

Create another table with an autoincrement primary key, then do an "INSERT INTO ... SELECT DISTINCT ..." for all the columns except the ID column (assuming it won't hurt anything if the ID changes).

ppowell

Yeah, unfortunately I can't do that either. I'd love to though 🙁 But there are several relational tables associated with the now-conjoined table that reside in legacy and changing the IDs would require changing those IDs as well, which would make for one mean query that I couldn't fathom how to write right now)

Phil

ppowell

I did find a rather novel approach to this, however:


-- field `unique_key` will contain a 16-character random alphanumeric string for repost protection
ALTER TABLE Spring.Students ADD UNIQUE (`unique_key`);

ALTER TABLE Summer.Students ADD UNIQUE (`unique_key`);

DROP TABLE /*! IF EXISTS */ AllStudents.spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ AllStudents.spring_summer  SELECT * FROM Spring.Students LIMIT 1;

DELETE FROM AllStudents.spring_summer;

INSERT INTO AllStudents.spring_summer SELECT * FROM Spring.Students UNION SELECT * FROM Summer.Students;

This produces the following syntax error:

Syntax error near 'UNION SELECT * FROM Summer.Students'

What gives? It was working over the weekend and now fails? WHY?

Phil