Post reply

Find partial double entries

  • August 31, 2004 at 8:38 am

    #112203

    Hi SQL geeks,

    Although I'm designing Access 2K applications linked to SQL2k server since a few months, today I face a problem that I'm a bit stuck with... I've a table which contains serial numbers generated by a small VBScript into the SQL DB. I've also some reports that allow the users to print out the Serial#'s selected by dates. The table structure looks like this :

    Column_name   Type     

    ------------------------

    ZEPID             int

    UPC                char

    SerialNo           char

    SerialDate        datetime

                 

    The serialnumbers are a mix of letters and numbers (ex: ZEP12345F404)

    The challenge now is that the last 4 positions of some of those Serial#'s have been changed into F405 on the items (PU plates) and the new numbers allready generated into the table (in the table, SerialNo must be unique). I need now to clean out the old numbers (like ZEP12345F404 replaced by ZEP12345F405). How can I perform this with a T-SQL script of even in Access ?

    Thanks in advance for your help to all.

    Beat

    ++++++++++
    Beat BUCHER
    SysAdmin
    Dynamics GP Consultant
    ++++++++++

  • August 31, 2004 at 9:10 am

    #521242

    Update Table_Name_Here Set SerialNo = LEFT(SerialNo, LEN(SerialNo)-4) + 'F405'

    Where place_your_criteria_here

     

    This should do the trick. Don't forget to insert your criteria in the where clause or all rows will updated.

     

    Cheers, Stephen

  • August 31, 2004 at 9:59 am

    #521251

    Hi Stephen,

    I guess you missed somthing in the original post . What I try to achieve is not the updating of the serialnumbers, but more to delete the ones that are identical whitin the 11 first chars, since the new number had allready been generated (the ones ending with ...F405).

    I need to clean out the the doubles that are ending with ...404 at then end and keep the good ones that are unique...

    Btw, Thanks in advance for your reply, I'll try to figure something out with that.

    Beat

    ++++++++++
    Beat BUCHER
    SysAdmin
    Dynamics GP Consultant
    ++++++++++

  • September 1, 2004 at 6:55 am

    #521340

    Help me to make sure I understand you correctly this time.

    1. You want to delete all duplicate SerialNo's that end with something other than 'F405'.

    2. Or, you want to delete all SerialNo's that end with something other than 'F405' and the first 11 char's match a SerialNo that ends with 'F405'?

    3. Both

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply