[RESOLVED] Still cannot combine 2 identical tables even with MySQL 5.0

ppowell

I need to combine db1.table1 and db2.table1 into db3.table1, even with MySQL 5.0 installation the query STILL fails:

DROP TABLE /*! IF EXISTS */ spring_summer;

CREATE TABLE /*! IF NOT EXISTS */ spring_summer TYPE=MyISAM SELECT DISTINCT * FROM Spring.Students ON DUPLICATE KEY UPDATE unique_key=unique_key UNION SELECT DISTINCT * FROM Summer.Students ON DUPLICATE KEY UPDATE unique_key=unique_key

Produces this error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON DUPLICATE KEY UPDATE unique_key=unique_key' at line 1

Am I not allowed to use "ON DUPLICATE KEY" in MySQL 5.0? I have to combine 2 tables together, however, the data in the tables can be "identical" (all fields identical except for ID) and if so, must be excluded from the CREATE TABLE SELECT transaction.

What on earth do I do now?

Thanx
Phil

Roger_Ramjet

Well now, reding your code, I'm as lost as you are. 😕

So lets start again.

the problem

2 databases
table with same structure in each db
records in may be duplicated within each table (forget id column)
records may be duplicated across both tables (forget id columns)
need to creat table in 3rd db with unique records by combining both tables

SOLUTION
create table in db3
create UNIQUE multi-column index on that table
insert select from db1 - index will reject duplicates
insert select from db2 - ditto

Learn to use the power os sql and relational databases: if you want to ensure unique data in a table then use an index ALWAYS

You only need to use DISTINCT when selecting data for display, not when storing data.

PS
AUTOINC columns are only for internal reference and should not be used in the outside world. We use them because integer indexes are much faster than other types, so they make table joins more efficient. They should never be used to refer to the actual data.

ppowell

Sorry but following your steps to the letter ensures that db3.table1 has only 1 record in the table; the moment the second record is attempted to be inserted I get the "duplicate entry 'asdfasdf' in line 1' error and it stops.

What will happen is that the "unique_key" field in db1.table1 (or db2.table1) is SUPPOSED to be unique, but due to a programming glitch it's not. The entries in that field can be duplicates even though they're not supposed to be duplicates, as a result, you will have records where the ID is different, but EVERYTHING ELSE is identical including "unique_key" which is supposed to be unique, but in this case it can't be.

That is my problem 🙁

Phil

Roger_Ramjet

Programming glitches have nothing to do with UNIQUE INDEXES in a database table: unless the database engine is stuffed.

The error message arrises because you have not included the 'IGNORE' keyword in your query. INSERT QUERY .

There are lots of ways to do most things, but usually only one or two that are worth using.

rule 1
If you want to guarantee that data in a table is unique then only a unique index is guaranteed to work.
Without it, you may write code that does not cause duplicates, but then the next guy writes bad code and all your efforts are wasted. I think your current predicament is proof of that.

You could do this with 2 simple select queries that use the DISTINCT keyword, but then your second query would have to use a left join with is null to elliminate duplicates across the tables. Difficult join to write when it is multi-column unqueness we are after.
You could also combine those 2 queries into 1 union query, but would still have to include the join/is null and that could get really ugly.

So my only choice would be 3 queries. 1 to create the table with the unique multi-column index, and then 2 simple selects with the ignore keyword.

ppowell

Could you please show me an example of the IGNORE keyword? I am absolutely not following you on this.

The idea I had perhaps is way complex but the only thing I can think of.

1) I will combine the two tables into 1 table
2) Go from top to bottom on table and check if the ID has relations (There are several relational tables to the combined table); if so, keep it
3) If there is an ID with duplicate data as another ID, check for
a) relations
b) No relations? Then min(ID) wins

That's all I can think of, nonetheless, please tell me more about IGNORE

Phil

Roger Ramjet wrote:
Programming glitches have nothing to do with UNIQUE INDEXES in a database table: unless the database engine is stuffed.

The error message arrises because you have not included the 'IGNORE' keyword in your query. INSERT QUERY .

There are lots of ways to do most things, but usually only one or two that are worth using.

rule 1
If you want to guarantee that data in a table is unique then only a unique index is guaranteed to work.
Without it, you may write code that does not cause duplicates, but then the next guy writes bad code and all your efforts are wasted. I think your current predicament is proof of that.

You could do this with 2 simple select queries that use the DISTINCT keyword, but then your second query would have to use a left join with is null to elliminate duplicates across the tables. Difficult join to write when it is multi-column unqueness we are after.
You could also combine those 2 queries into 1 union query, but would still have to include the join/is null and that could get really ugly.

So my only choice would be 3 queries. 1 to create the table with the unique multi-column index, and then 2 simple selects with the ignore keyword.

Roger_Ramjet

$query1 = "INSERT IGNORE INTO db3.t1 SELECT * FROM db1.t1";
$query2 = "INSERT IGNORE INTO db3.t1 SELECT * FROM db2.t1";

As simple as that.

Now, the one thing you don't actually want are those autoinc id fields, so you'll need to refine the queries to select just the required columns, or just allow that column without any index and drop it afterwards.

This also relies on there being valid data in the exisitng tables as the one drawback from the IGNORE keyword is that columns with invalid data according to any column restrictions, will have that data adjusted. But then that should not apply here now should it.

Now, even if you were to use some other method to elliminate duplicates as you suggest, you MUST create the UNIQUE index to ensure data integrity in the future. So if that is the case then why not use it now. After all, it's what relational databases are designed to do, and what sql does very well.

ppowell

Thank you for helping, "IGNORE" now makes perfect sense in light of how you used it!!

It worked PERFECTLY BTW!

Phil