|
 |
access_asp thread: Deleting all but one mutiple records
Message #1 by "ANTONIO JONES" <ajjones13@m...> on Tue, 7 May 2002 12:38:59
|
|
I'm trying to delete all mutiple records except one base on the orderID.
With one orderID there should not be any duplicate productID. Having
problems with the code below.
sqlText = ("DELETE orderID, productID, quantity, pname " _
& " FROM itemsordered " _
& " WHERE itemsordered.orderID IN " _
& " (SELECT itemsordered.productID " _
& " FROM itemsordered AS itemsordered " _
& " WHERE Exists (SELECT orderID, productID, Count(productID) " _
& " FROM itemsordered " _
& " WHERE orderID = orderID " _
& " AND productID = productID " _
& " GROUP BY itemsordered.orderID, itemsordered.productID " _
& " HAVING Count(itemsordered.productID) > 1)) " _
& " AND itemsordered.orderID NOT IN " _
& " (SELECT Min(productID) "_
& " FROM itemsordered AS F "_
& " WHERE Exists (SELECT orderID, productID, Count(productID) "_
& " FROM itemsordered "_
& " WHERE itemsordered.orderID = F.orderID "_
& " AND itemsordered.productID = F.productID "_
& " GROUP BY itemsordered.orderID, itemsordered.productID "_
& " HAVING Count(itemsordered.productID) > 1) "_
& " GROUP BY orderID, productID) ")
Conn.Execute(sqlText)
Secondly, if there is a way to add the quantities of the duplicate
productID's by orderID.
Thanks!!
Message #2 by "ANTONIO JONES" <ajjones13@m...> on Tue, 7 May 2002 13:52:26
|
|
I got this to work but it deletes both records if there is a duplicate.
How do I delete all except one?
Update Code:
sqlText = ("DELETE orderID, productID, quantity, pname " _
& " FROM itemsordered " _
& " WHERE itemsordered.productID IN " _
& " (SELECT F.productID " _
& " FROM itemsordered AS F " _
& " WHERE Exists (SELECT orderID, productID, Count(productID) " _
& " FROM itemsordered " _
& " WHERE itemsordered.orderID = F.orderID " _
& " AND itemsordered.productID = F.productID " _
& " GROUP BY itemsordered.orderID, itemsordered.productID " _
& " HAVING Count(itemsordered.productID) > 1)) " _
& " AND itemsordered.orderID NOT IN " _
& " (SELECT Min(productID) "_
& " FROM itemsordered AS F "_
& " WHERE Exists (SELECT orderID, productID, Count(productID) "_
& " FROM itemsordered "_
& " WHERE itemsordered.orderID = F.orderID "_
& " AND itemsordered.productID = F.productID "_
& " GROUP BY itemsordered.orderID, itemsordered.productID "_
& " HAVING Count(itemsordered.productID) > 1) "_
& " GROUP BY orderID, productID) ")
Conn.Execute(sqlText)
>
S> econdly, if there is a way to add the quantities of the duplicate
p> roductID's by orderID.
T> hanks!!
Message #3 by "Ken Schaefer" <ken@a...> on Wed, 8 May 2002 14:59:50 +1000
|
|
Add a NOT EXISTS clause and check to see that there isn't a duplicate record
with a higher value for the primary key. If there isn't a higher value, then
keep the current record. If there is a higher value then delete the current
record.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "ANTONIO JONES" <ajjones13@m...>
Subject: [access_asp] Re: Deleting all but one mutiple records
: I got this to work but it deletes both records if there is a duplicate.
: How do I delete all except one?
:
: Update Code:
:
: sqlText = ("DELETE orderID, productID, quantity, pname " _
: & " FROM itemsordered " _
: & " WHERE itemsordered.productID IN " _
:
:
: & " (SELECT F.productID " _
: & " FROM itemsordered AS F " _
: & " WHERE Exists (SELECT orderID, productID, Count(productID) " _
: & " FROM itemsordered " _
: & " WHERE itemsordered.orderID = F.orderID " _
: & " AND itemsordered.productID = F.productID " _
: & " GROUP BY itemsordered.orderID, itemsordered.productID " _
: & " HAVING Count(itemsordered.productID) > 1)) " _
: & " AND itemsordered.orderID NOT IN " _
:
: & " (SELECT Min(productID) "_
: & " FROM itemsordered AS F "_
: & " WHERE Exists (SELECT orderID, productID, Count(productID) "_
: & " FROM itemsordered "_
: & " WHERE itemsordered.orderID = F.orderID "_
: & " AND itemsordered.productID = F.productID "_
: & " GROUP BY itemsordered.orderID, itemsordered.productID "_
: & " HAVING Count(itemsordered.productID) > 1) "_
: & " GROUP BY orderID, productID) ")
:
: Conn.Execute(sqlText)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "ANTONIO JONES" <ajjones13@m...> on Thu, 9 May 2002 12:56:48
|
|
Add that clause before I go into the select statements? Could you give me
a small example?
> Add a NOT EXISTS clause and check to see that there isn't a duplicate
record
with a higher value for the primary key. If there isn't a higher value,
then
keep the current record. If there is a higher value then delete the current
record.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "ANTONIO JONES" <ajjones13@m...>
Subject: [access_asp] Re: Deleting all but one mutiple records
: I got this to work but it deletes both records if there is a duplicate.
: How do I delete all except one?
:
: Update Code:
:
: sqlText = ("DELETE orderID, productID, quantity, pname " _
: & " FROM itemsordered " _
: & " WHERE itemsordered.productID IN " _
:
:
: & " (SELECT F.productID " _
: & " FROM itemsordered AS F " _
: & " WHERE Exists (SELECT orderID, productID, Count(productID) " _
: & " FROM itemsordered " _
: & " WHERE itemsordered.orderID = F.orderID " _
: & " AND itemsordered.productID = F.productID " _
: & " GROUP BY itemsordered.orderID, itemsordered.productID " _
: & " HAVING Count(itemsordered.productID) > 1)) " _
: & " AND itemsordered.orderID NOT IN " _
:
: & " (SELECT Min(productID) "_
: & " FROM itemsordered AS F "_
: & " WHERE Exists (SELECT orderID, productID, Count(productID) "_
: & " FROM itemsordered "_
: & " WHERE itemsordered.orderID = F.orderID "_
: & " AND itemsordered.productID = F.productID "_
: & " GROUP BY itemsordered.orderID, itemsordered.productID "_
: & " HAVING Count(itemsordered.productID) > 1) "_
: & " GROUP BY orderID, productID) ")
:
: Conn.Execute(sqlText)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #5 by "Ken Schaefer" <ken@a...> on Fri, 10 May 2002 13:40:58 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "ANTONIO JONES" <ajjones13@m...>
Subject: [access_asp] Re: Deleting all but one mutiple records
: Add that clause before I go into the select statements? Could you give me
: a small example?
:
:
: Add a NOT EXISTS clause and check to see that there isn't a duplicate
: record with a higher value for the primary key. If there isn't a higher
value,
: then keep the current record. If there is a higher value then delete the
current
: record.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Antonio,
SQL is very simple (there are very few keywords), yet can seem very
daunting. The key to understanding SQL is to know that it works using sets.
So given a duplicate field(s), we want to:
a) assemble a set with the highest (or lowest) primary key value for a given
value in the duplicate field(s)
b) delete all the records in the total set of records that are not in the
subset in {a}
Suppose we have the table:
Table: TABLE1
Fields: TableID, Field1
TableID Field1
1 abc
2 abc
3 abc
4 xyz
5 def
6 def
Set {a} would be:
3 abc
4 xyz
6 def
We could assemble this set like so:
SELECT
a.TableID,
a.Field1
FROM
Table1 AS a
WHERE NOT EXISTS
(
SELECT
NULL
FROM
Table1 AS b
WHERE
b.TableID > a.TableID
AND
b.Field1 = a.Field1
)
Now, if we want to use the results of this query as a subquery within
another query, then we could do something like:
DELETE FROM
Table1 AS a
WHERE
a.TableID NOT IN
(
SELECT
b.TableID
FROM
Table1 AS b
WHERE NOT EXISTS
(
SELECT
NULL
FROM
Table1 AS c
WHERE
c.TableID > b.TableID
AND
c.Field1 = b.Field1
)
)
HTH
Cheers
Ken
Message #6 by "ANTONIO JONES" <ajjones13@m...> on Mon, 13 May 2002 20:57:02
|
|
> I'm trying to delete all mutiple records except one base on the orderID.
W> ith one orderID there should not be any duplicate productID. Having
p> roblems with the code below.
>
s> qlText = ("DELETE orderID, productID, quantity, pname " _
> & " FROM itemsordered " _
> & " WHERE itemsordered.orderID IN " _
>
> & " (SELECT itemsordered.productID " _
> & " FROM itemsordered AS itemsordered " _
> & " WHERE Exists (SELECT orderID, productID, Count(productID) " _
> & " FROM itemsordered " _
> & " WHERE orderID = orderID " _
> & " AND productID = productID " _
> & " GROUP BY itemsordered.orderID, itemsordered.productID " _
> & " HAVING Count(itemsordered.productID) > 1)) " _
> & " AND itemsordered.orderID NOT IN " _
> & " (SELECT Min(productID) "_
> & " FROM itemsordered AS F "_
> & " WHERE Exists (SELECT orderID, productID, Count(productID) "_
> & " FROM itemsordered "_
> & " WHERE itemsordered.orderID = F.orderID "_
> & " AND itemsordered.productID = F.productID "_
> & " GROUP BY itemsordered.orderID, itemsordered.productID "_
> & " HAVING Count(itemsordered.productID) > 1) "_
> & " GROUP BY orderID, productID) ")
> Conn.Execute(sqlText)
>
S> econdly, if there is a way to add the quantities of the duplicate
p> roductID's by orderID.
T> hanks!!
Message #7 by "ANTONIO JONES" <ajjones13@m...> on Mon, 13 May 2002 21:03:33
|
|
Reconfigured the code you wrote. Still getting an error, am I on the
right track? This is what set {a} should look like. Does the code agree?
orderID productID
1 abc
1 cba
3 abc
4 xyz
5 def
5 def
6 jkl
6 jkl
Set {a} would be:
1 abc
1 cba
3 abc
4 xyz
5 def
6 jkl
sqlText = "SELECT a.orderID, a.productID FROM itemsordered AS a WHERE NOT
EXISTS " _
& " (SELECT * " _
& " FROM itemsordered as b " _
& " WHERE b.orderID > a.orderID" _
& " AND b.productID = a.productID) "
Conn.Execute(sqlText)
sqlText = " DELETE * FROM itemsordered as a " _
& " WHERE a.orderID NOT IN " _
& " (SELECT b.orderID " _
& " FROM itemsordered AS b WHERE NOT EXISTS "_
& " SELECT * FROM itemsordered as c WHERE "_
& " c.orderID > b.orderID "_
& " AND c.productID = b.productID)"
Conn.Execute(sqlText)
I'm trying to delete all mutiple records except one base on the orderID.
W> ith one orderID there should not be any duplicate productID. Having
p> roblems with the code below.
>
s> qlText = ("DELETE orderID, productID, quantity, pname " _
> & " FROM itemsordered " _
> & " WHERE itemsordered.orderID IN " _
>
> & " (SELECT itemsordered.productID " _
> & " FROM itemsordered AS itemsordered " _
> & " WHERE Exists (SELECT orderID, productID, Count(productID) " _
> & " FROM itemsordered " _
> & " WHERE orderID = orderID " _
> & " AND productID = productID " _
> & " GROUP BY itemsordered.orderID, itemsordered.productID " _
> & " HAVING Count(itemsordered.productID) > 1)) " _
> & " AND itemsordered.orderID NOT IN " _
> & " (SELECT Min(productID) "_
> & " FROM itemsordered AS F "_
> & " WHERE Exists (SELECT orderID, productID, Count(productID) "_
> & " FROM itemsordered "_
> & " WHERE itemsordered.orderID = F.orderID "_
> & " AND itemsordered.productID = F.productID "_
> & " GROUP BY itemsordered.orderID, itemsordered.productID "_
> & " HAVING Count(itemsordered.productID) > 1) "_
> & " GROUP BY orderID, productID) ")
> Conn.Execute(sqlText)
>
S> econdly, if there is a way to add the quantities of the duplicate
p> roductID's by orderID.
T> hanks!!
Message #8 by "Ethan Selzer" <ethanselzer@a...> on Mon, 13 May 2002 14:22:50 -0700
|
|
Hi Antonio,
It's a good practice to include any error message you receive in your post.
I think your DELETE statement is missing parenthesis for one of the derived
tables. Try this:
sqlText = " DELETE * FROM itemsordered as a " _
& " WHERE a.orderID NOT IN " _
& " (SELECT b.orderID " _
& " FROM itemsordered AS b WHERE NOT EXISTS "_
& " ( SELECT * FROM itemsordered as c WHERE "_
& " c.orderID > b.orderID "_
& " AND c.productID = b.productID))"
-----Original Message-----
From: ANTONIO JONES [mailto:ajjones13@m...]
Sent: Monday, May 13, 2002 9:04 PM
To: Access ASP
Subject: [access_asp] Re: Deleting all but one mutiple records
Reconfigured the code you wrote. Still getting an error, am I on the
right track? This is what set {a} should look like. Does the code agree?
orderID productID
1 abc
1 cba
3 abc
4 xyz
5 def
5 def
6 jkl
6 jkl
Set {a} would be:
1 abc
1 cba
3 abc
4 xyz
5 def
6 jkl
sqlText = "SELECT a.orderID, a.productID FROM itemsordered AS a WHERE NOT
EXISTS " _
& " (SELECT * " _
& " FROM itemsordered as b " _
& " WHERE b.orderID > a.orderID" _
& " AND b.productID = a.productID) "
Conn.Execute(sqlText)
sqlText = " DELETE * FROM itemsordered as a " _
& " WHERE a.orderID NOT IN " _
& " (SELECT b.orderID " _
& " FROM itemsordered AS b WHERE NOT EXISTS "_
& " SELECT * FROM itemsordered as c WHERE "_
& " c.orderID > b.orderID "_
& " AND c.productID = b.productID)"
Conn.Execute(sqlText)
Message #9 by "ANTONIO JONES" <ajjones13@m...> on Tue, 14 May 2002 12:22:59
|
|
Thanks, that cleared up the error..but I'm still get duplicate records. I
tried to put in Count(c.productID) >1 but I get an no aggregate statement
in WHERE clause. Is there a way around this?
> Hi Antonio,
It's a good practice to include any error message you receive in your post.
I think your DELETE statement is missing parenthesis for one of the derived
tables. Try this:
sqlText = " DELETE * FROM itemsordered as a " _
& " WHERE a.orderID NOT IN " _
& " (SELECT b.orderID " _
& " FROM itemsordered AS b WHERE NOT EXISTS "_
& " ( SELECT * FROM itemsordered as c WHERE "_
& " c.orderID > b.orderID "_
& " AND c.productID = b.productID))"
-----Original Message-----
From: ANTONIO JONES [mailto:ajjones13@m...]
Sent: Monday, May 13, 2002 9:04 PM
To: Access ASP
Subject: [access_asp] Re: Deleting all but one mutiple records
Reconfigured the code you wrote. Still getting an error, am I on the
right track? This is what set {a} should look like. Does the code agree?
orderID productID
1 abc
1 cba
3 abc
4 xyz
5 def
5 def
6 jkl
6 jkl
Set {a} would be:
1 abc
1 cba
3 abc
4 xyz
5 def
6 jkl
sqlText = "SELECT a.orderID, a.productID FROM itemsordered AS a WHERE NOT
EXISTS " _
& " (SELECT * " _
& " FROM itemsordered as b " _
& " WHERE b.orderID > a.orderID" _
& " AND b.productID = a.productID) "
Conn.Execute(sqlText)
sqlText = " DELETE * FROM itemsordered as a " _
& " WHERE a.orderID NOT IN " _
& " (SELECT b.orderID " _
& " FROM itemsordered AS b WHERE NOT EXISTS "_
& " SELECT * FROM itemsordered as c WHERE "_
& " c.orderID > b.orderID "_
& " AND c.productID = b.productID)"
Conn.Execute(sqlText)
Message #10 by "Ken Schaefer" <ken@a...> on Wed, 15 May 2002 11:14:01 +1000
|
|
You have a problem with your table - you have no candidate primary key.
Because you have no primary candidate key there is no way that you can
uniquely identify a record.
What you will need to do is do a SELECT DISTINCT() on this table to get the
distinct OrderID + ProductID combinations and INSERT them into a *new*
table, and drop the old table.
If you have a table with two records like this:
orderID productID
6 jkl
6 jkl
there is no way that you can delete one and keep the other, because, to the
database there is no way to distinguish between them.
The other alternative is to temporarily add another column to the table (eg
an autonumber), so that each record has a unique recordID. Then you can use
the SQL that I posted before to eliminate the duplicate records.
Personally I suggest that you fix this table ASAP, then you create a primary
key for this table (a combination of product ID and orderID) so that you
don't have this problem again in the future!
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "ANTONIO JONES" <ajjones13@m...>
To: "Access ASP" <access_asp@p...>
Sent: Monday, May 13, 2002 9:03 PM
Subject: [access_asp] Re: Deleting all but one mutiple records
: Reconfigured the code you wrote. Still getting an error, am I on the
: right track? This is what set {a} should look like. Does the code agree?
:
: orderID productID
: 1 abc
: 1 cba
: 3 abc
: 4 xyz
: 5 def
: 5 def
: 6 jkl
: 6 jkl
:
: Set {a} would be:
: 1 abc
: 1 cba
: 3 abc
: 4 xyz
: 5 def
: 6 jkl
:
: sqlText = "SELECT a.orderID, a.productID FROM itemsordered AS a WHERE NOT
: EXISTS " _
: & " (SELECT * " _
: & " FROM itemsordered as b " _
: & " WHERE b.orderID > a.orderID" _
: & " AND b.productID = a.productID) "
:
: Conn.Execute(sqlText)
:
: sqlText = " DELETE * FROM itemsordered as a " _
: & " WHERE a.orderID NOT IN " _
: & " (SELECT b.orderID " _
: & " FROM itemsordered AS b WHERE NOT EXISTS "_
: & " SELECT * FROM itemsordered as c WHERE "_
: & " c.orderID > b.orderID "_
: & " AND c.productID = b.productID)"
:
: Conn.Execute(sqlText)
:
:
:
:
:
:
: I'm trying to delete all mutiple records except one base on the orderID.
: W> ith one orderID there should not be any duplicate productID. Having
: p> roblems with the code below.
: >
: s> qlText = ("DELETE orderID, productID, quantity, pname " _
: > & " FROM itemsordered " _
: > & " WHERE itemsordered.orderID IN " _
:
: >
: > & " (SELECT itemsordered.productID " _
: > & " FROM itemsordered AS itemsordered " _
: > & " WHERE Exists (SELECT orderID, productID, Count(productID) " _
: > & " FROM itemsordered " _
: > & " WHERE orderID = orderID " _
: > & " AND productID = productID " _
: > & " GROUP BY itemsordered.orderID, itemsordered.productID " _
: > & " HAVING Count(itemsordered.productID) > 1)) " _
: > & " AND itemsordered.orderID NOT IN " _
:
: > & " (SELECT Min(productID) "_
: > & " FROM itemsordered AS F "_
: > & " WHERE Exists (SELECT orderID, productID, Count(productID) "_
: > & " FROM itemsordered "_
: > & " WHERE itemsordered.orderID = F.orderID "_
: > & " AND itemsordered.productID = F.productID "_
: > & " GROUP BY itemsordered.orderID, itemsordered.productID "_
: > & " HAVING Count(itemsordered.productID) > 1) "_
: > & " GROUP BY orderID, productID) ")
:
: > Conn.Execute(sqlText)
:
: >
: S> econdly, if there is a way to add the quantities of the duplicate
: p> roductID's by orderID.
: T> hanks!!
:
|
|
|
View Cart
