Cross table MySQL Query help

[XMLMania]

Hi,

I have a problem were I have 4 identically structured tables containing member data and I need unique rows across each.

For an individual table I would perform:
SELECT id, COUNT(id) AS count FROM members_9_2004 GROUP BY username, nickname HAVING count > 1

Then I would delete the data that occurs more than once.

Now a problem has erose were the same data may occur across tables.

Can anyone help?

[Salsa]

Since your tables all have identical structures, if they are MYISAM type tables you could use a MERGE type table. To do this, create yet another table also with the same structure, but make its type MERGE instead MYISAM. eg:

CREATE TABLE new_table_name (
 ...
 same data structure
 ...
) TYPE = MERGE UNION = (table1, table2, table3, table4)
INSERT_METHOD = [NO, FIRST, or LAST]; # ***

***optional declaration to forbid INSERTs or to determine which table to make INSERTs into

A SELECT query to this table will query all the constituent tables in the UNION.

MERGE type tables also support DELETE, UPDATE and INSERT. When you find duplicates, a DELETE query to the MERGE table would DELETE from all the tables, then you could reINSERT into one of them.

I hope this helps

[XMLMania]

Haha sure does help! Thats awesome, I was scared it would be slow cause I had to do it on the production machine... but it was lightening fast.

Muchos gracias!