Welcome to WebmasterWorld Guest from 220.127.116.11
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?
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