FrenchTech SAAS

Although XML documents can describe structured data, an NXD containing many XML documents might not be the best solution to describe relationships between structured data divided over multiple documents and maintain referential integrity. If it is already possible to manage such relationships (perhaps using XPointer), the usage is not standardized.

The best advice is to use the right tool for the right job. However, coexistence of an NXD with an RDBMS creates integration challenges. The pureXML features of DB2 combine the best of both worlds and allow the storage of both structured and semi-structured data. For the storage of unstructured data, NXDs and RDBMSs are equally suited.

With MySQL  we do not have the tools that Oracle has to easily find the duplicates. But most MySql tables use an auto-increment ID field that helps us to identify the duplicates.

Simple Method

As with the Oracle method use the “greater than” sign to to keep the earliest row entered. We can change this  to pull the latest  by replacing “greater than” with “less than”.

DELETE A FROM [TABLE] as A, [TABLE] as B
WHERE A.[UNIQUE FIELD(S)] = B.[UNIQUE FIELD(S)]
AND A.ID > B.ID;

Without the ID field

Now it gets complicated in MySQL, we need to create a table with an unique ID then remove the duplicates. Once the dups have been remove  then  we can put the data back onto the original table.

Drop the dups table if it exists.

DROP TABLE IF EXISTS [TABLE]_dups;

Create the dups table.  This table will be the base table(table with dups) with the ID added.

CREATE TABLE [TABLE]_dups (
id INT(11) default NULL auto_increment,
[ALL TABLE COLUMNS],
PRIMARY KEY (id)
);

Insert into the dups table from the base table.

INSERT INTO [TABLE]_dups
SELECT NULL,[UNIQUE FIELD(S)]
FROM [TABLE];

Delete the dups using the SQL we used in the prior example

DELETE A FROM [TABLE]_dups as A, [TABLE]_dups as B
WHERE A.[UNIQUE FIELD(S)] = B.[UNIQUE FIELD(S)]
AND A.ID < B.ID;

Delete the Base table

DELETE FROM [TABLE];

Insert into the base table from the dups table.

INSERT INTO [TABLE]
SELECT [all columns less the ID field]
FROM [TABLE]_dups;

Remove the dups table

DROP TABLE [TABLE]_dups;

I hope this posts help you when you run into this problem… and we all run into this problem.

Chapter 6 of “SQL Design Patterns” book summarized different methods in the following feature matrix:

Adjacency relation (tree edges; standalone, or combined with the tree nodes)Nested SetsMaterialized PathNested Intervals via Matrix encodingHave to use proprietory SQL extensions for finding ancestors and descendants; although the queries are efficientStandard SQLStandard SQLStandard SQLFinding descendants is relatively efficient (i.e. proportional to the size of the subtree)Finding descendants is easy and relatively efficient (i.e. proportional to the size of the subtree)Finding descendants is easy and relatively efficient (i.e. proportional to the size of the subtree)Same as MP: Finding descendants is easy and relatively efficientFinding ancestors is efficientFinding ancestors is easy but inefficientFinding ancestors is tricky but efficientSame as MP: Finding ancestors is tricky but efficientFinding node’s children is trivialFinding node’s children as all the descendants restricted to the next level is inefficient (e.g. consider root node)Finding node’s children as descendants on next level is inefficientSame as AR: Finding node’s children is trivialFinding node’s parent is trivialFinding node’s parent as ancestor on the previous level is inefficient due to inefficiency of ancestors searchFinding node’s parent as ancestor on the previous level is efficientSame as AR: Finding node’s parent is trivialAggregate queries are relatively efficient (i.e. proportional to the size of the subtree)Aggregate queries are relatively efficient (except counting, which is super fast)!Aggregate queries are relatively efficient (i.e. proportional to the size of the subtree)Aggregate queries are relatively efficient (i.e. proportional to the size of the subtree)tree reorganization is very simpletree reorganization is hardtree reorganization is easytree reorganization is easy (but not as simple as in AR)

After the book were published I found some wrong assertions in this matrix as well.