|
Introduction
This article offers an analysis of Structured Query Language (SQL) and presents techniques that can be used in building SQL statements. People tend to think of SQL as a programming language, but in reality, SQL is a Set language. One could say SQL is Set Theory or Relational Algebra; these terms are interchangeable. Some people think of this in terms of Venn diagrams or DeMorgan's Law. Although those can be quite complicated, the basic forms are not difficult to understand. The cases presented here are intended for certain circumstances, but they can be applied to many situations. Each article in this four-part series will address one case.
Deleting Duplicate Records
The following is an analysis of records in a table, some of which may be duplicated. Many different things can be accomplished with this method, including looking for duplicates, looking for records that are duplicated n times, deleting duplicates, etc.
People develop many ways to delete duplicate records from a table. You can make a new table and select the unique records into that table. You can concatenate fields together, get a distinct representation of those records, and place those in another table. But these methods are normally used when people think they do not have any other way to accomplish this.
Let's suppose we have a table structure with a multiple field Primary Key. The key is from an old ordering system we imported into a table that includes an IDENTITY field:
CREATE TABLE SHIPMENT_ORDERS (
[id] [int] IDENTITY (1, 1) NOT NULL
, [shipment] [varchar] (20) NOT NULL
, [purchase_order] [varchar] (20) NOT NULL
, [item_sku] [char] (20) NOT NULL
, [units_ordered] [int] NOT NULL
, [units_shipped] [int] NOT NULL
, [shipment_date] [datetime] NOT NULL
, [order_date] [datetime] NOT NULL
, [last_name] [varchar] (30) NOT NULL
, [first_name] [varchar] (30) NOT NULL
, [full_address] [varchar] (30) NOT NULL
, [email] [varchar] (30) NOT NULL
)
For sake of argument, there is no Primary Key on this table. Let's assume that some duplicate data was introduced into it. For our discussion, we will assume a target Primary Key to be [shipment], [purchase_order], [item_sku]. We want to get some statistical information from the data first so our end goal will be to remove the duplicate records. I will now provide the base query for all of our operations:
/* 1. Query heading */
SELECT
* /* Remember to never use SELECT * and write out the field names */
FROM
/* 2. Table with duplicates */
SHIPMENT_ORDERS
WHERE
EXISTS (
SELECT
NULL
FROM
/* 3. Table with duplicates, with an alias */
SHIPMENT_ORDERS b
WHERE
/* 4. Join each field with *itself*. These are fields that could be Primary Keys */
b.[shipment] = SHIPMENT_ORDERS.[shipment]
AND b.[purchase_order] = SHIPMENT_ORDERS.[purchase_order]
AND b.[item_sku] = SHIPMENT_ORDERS.[item_sku]
GROUP BY
/* 5. I must GROUP BY these fields because of the HAVING
clause and because these are the possible PK */
b.[shipment], b.[purchase_order], b.[item_sku]
HAVING
/* 6. This is the determining factor. We can control our
output from here. In this case, we want to pick records
where the ID is less than the MAX ID */
SHIPMENT_ORDERS.[id] < MAX(b.[id])
)
- The kind of query we want will be either SELECT * or DELETE FROM. We will view our results first, then change this to delete the offending records if necessary.
- This is the table we are performing the above action on.
- This is the same table, duplicated. We are self-referencing our target table.
- We must compare each field to its copy. This is 1,000 times better than concatenation, since we do not need to worry about the data lining up. This can be used when you have two (or more) tables you need to compare that have similar data. The way most people do this is concatenate all target fields together. That is the most intensive thing you can do when comparing a group of fields in a table.
- We need to GROUP BY the same fields because we want to ensure that our data is unique in our analysis.
- This ultimately determines what our condition for the resulting data will be.
After running this query, we would see all of the duplicated records. For instance, if we had the following records in the table:
id shipment purchase_order item_sku
3 435436 A123765 453987001201
46 435436 A123765 453987001201
354 435436 A123765 453987001201
23 981123 C543219 843209132209
613 981123 C543219 843209132209
the result after running the above query would be:
id shipment purchase_order item_sku
3 435436 A123765 453987001201
46 435436 A123765 453987001201
23 981123 C543219 843209132209
This would be a report of the duplicated records that are present in the data. Now, if we look at section 6 in the query:
SHIPMENT_ORDERS.[id] < MAX(b.[id])
we can see that an alternate query could use MIN() instead of MAX(). Note that this would not affect the outcome, except it would pick the minimum of the field instead of the maximum. In the case of our table, the only field that is not duplicated is [id]. If the table we were dealing with had a field such as a datetime field, it could be used as a pseudo-IDENTITY field.
Suppose we want to get a report of all records that have a single, duplicated record. This is easily obtainable by changing one part of this query (6):
COUNT(b.[id]) = 2
Based on the previous data, our output would be:
id shipment purchase_order item_sku
23 981123 C543219 843209132209
613 981123 C543219 843209132209
Notice that we get both records.
Remember the first query we ran? It showed all of the duplicated records in the table. If we changed the first two words (SELECT *) in the SQL sentence to DELETE, we accomplish our goal of removing all duplicates from the table.
id shipment purchase_order item_sku
354 435436 A123765 453987001201
613 981123 C543219 843209132209
Conclusion
For a recap, we did not:
- create a new table
- concatenate fields
- use the DISTINCT keyword
- alter the table
and we did:
- use the original table to compare
- compare field to field without altering them
- rely on the existence of data, not generate new data
- use the IDENTITY field to ensure unique records
This query template can be used for many different types of reports and operations on data. At first look, that query may seem rather complicated, but when it is dissected, the sections are not complex at all. The next article will discuss the uses/differences of UNION and UNION ALL on single-table and multiple-table instances.
Remember, Set Theory is cool!
About the Author
David L. Penton, MCP, lives in Dallas and has a bachelor's degree in Mathematics
from Northwestern State University. For the last two years, he has been the
technical lead for the International Systems Group at JCPenney, Inc., in
Dallas. Before that, he was a senior business and analysis analyst for
Lockheed Martin Missile and Fire Control, also in Dallas. During that time he has
worked on various technology, including but not limited to ASP, SQL, VB,
C++, ColdFusion, and VB .NET/C# with the related web technologies
(JavaScript, HTML, DHTML, XHTML, CSS1/2). Database platforms he has dealt
with (development and admin) include but not limited to SQL Server 6.5/7/8,
Oracle 7/8, DB2, MS Access (from version 2).
David also participates in discussion lists hosted by
15Seconds.com, aspfriends.com,
Swynk, www.dfwcfug.org, and
lsoft.com.
He can be reached by email at lemans70@home.com and dpenton@jcpenney.com.
|
