| Cross table MySQL Query help
|
XMLMania
msg:1292787 | 7:14 pm on Nov 7, 2004 (gmt 0) |
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
msg:1292788 | 12:35 am on Nov 8, 2004 (gmt 0) |
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
msg:1292789 | 11:25 am on Nov 8, 2004 (gmt 0) |
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!
|
|
|
