Posted by serge on 06/06/05 23:31

> Below is a script that almost produces the result you are asking for.
> The difference from your reuslts table is that Product 4 now gets the
> name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
> each other, my guess is that it doesn't matter. If it does, it can
> probably be handled, but I didn't want to spend time to find how it
> it's not needed.

Actually it seems that the name does matter. There is a unique index on
the NAME column on tblProducts. You make your code look easy and
simple to write. I tried to modify this part of the code below to exclude
matching names from the selection list by adding a JOIN to tblProducts.
I was unsuccessful after 3 hours of trying. I have people pressing me to
give up writing code and upgrade the data manually one record at a time.
What a nightmare!

I then thought about deleting temporarily the Unique Index and running the
code which will create duplicate records without any error. Then the UPDATE
Names code I believe is fixing/patching things up. However i am at this
point
unsure if i will have to face a new problem when it comes to the other CHILD
table references that I have to create clone records.

> SELECT a.ProductID, a.UniqueID, a.Name
> FROM tblProductDetails a
> WHERE NOT EXISTS (SELECT *
> FROM (SELECT id = MIN(UniqueID)
> FROM tblProductDetails
> GROUP BY ProductID) AS b
> WHERE a.UniqueID = b.id)
> ORDER BY a.UniqueID

If you believe you can update the code very easily to handle the Name
matter,
would it be possible to do it please?

Thank you

[Back to original message]