Welcome to WebmasterWorld Guest from

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Cross table MySQL Query help



7:14 pm on Nov 7, 2004 (gmt 0)

10+ Year Member


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?


12:35 am on Nov 8, 2004 (gmt 0)

10+ Year Member

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)

***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


11:25 am on Nov 8, 2004 (gmt 0)

10+ Year Member

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!


Featured Threads

Hot Threads This Week

Hot Threads This Month