untach rows

  • Thread starter Thread starter JIM.H.
  • Start date Start date
J

JIM.H.

Hello,
I have two tables with the fields name,id,desc. The rows
might be duplicated in each table, I need to look at first
table and delete those rows in the second table. I could
not use unmatch query because it deletes all the rows in
the second table without checking if it is duplicated or
not. If first table contains a row once and it appears in
table2 twice, only first row should be deleted in table2,
not both.
Thanks,
Jim.
 
Dear Jim:

First, please define what you mean by "duplicate." Does this mean two
rows with the same id, or two rows with ALL the columns identical. If
you mean the former, then understand that you will be losing
information if you delete a row with any column unmatched. Make
sense?

Now, if two rows are identical, there is no way you can call one of
them the "first" and another "second." Identical rows (based on all
columns being identical) cannot be placed in any order, no matter how
hard you try. Rows are in an order only when you sort them, and
identical rows do not sort in any order. Just because they display
one at a time, don't let this trick you into believing they are in
some particular order.

If you wish to eliminate all completely identical rows from a table,
you can SELECT DISTINCT * FROM TableName and then make an append query
from that. Append them to a new, empty table of the same structure.
Other than the loss of the count of how many copies of each one you
had, you lose no information in this way.

To see which rows are found in two tables, join them on all the
columns that form your definition of "duplicate," whether that is just
the id column or all columns. Make this an outer join by choosing the
appropriate join type in the properties dialog. The columns for the
table on the "include only those rows" side of this join will be NULL
when no row is found, and you can filter this to show only those rows
of the other table where no rows is found in the "include only those
rows" side.

Can you understand what I have proposed? Please get back.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
JIM.H. said:
Hello,
I have two tables with the fields name,id,desc. The rows
might be duplicated in each table, I need to look at first
table and delete those rows in the second table. I could
not use unmatch query because it deletes all the rows in
the second table without checking if it is duplicated or
not. If first table contains a row once and it appears in
table2 twice, only first row should be deleted in table2,
not both.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Put an AutoNumber (named: AutoID) field on Table2 - it should not be in
random order. The idea is to have a unique ID for all T2's rows. Then
try this query (untested):

DELETE T2.*
FROM table2 As T2
WHERE EXISTS (SELECT * FROM table1 As T1 WHERE T1.name=T2.name AND
T1.id=T2.id AND T1.desc=T2.desc)
AND T2.AutoID > (SELECT Min(AutoID) FROM table2 As T3 WHERE
T3.name=T2.name AND T3.id=T2.id AND T3.desc=T2.desc))

To test this before running substitute "SELECT T2.*" for "DELETE T2.*."
That way you'll be able to see which records are going to be deleted.

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGZO3oechKqOuFEgEQIgvgCghKgJ3RH7smWluh26iCnBlwEQh7kAoOCP
mS/FGsY3dRg047U7EU088yPK
=P+e7
-----END PGP SIGNATURE-----
 
Back
Top