Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    1

    Unanswered: Help Needed for Data Cleanup Using UPDATE

    I am assisting in purging sensitive data out of an Access 97 mdb file. I would like to execute multiple UPDATEs rather than one at a time. Is it possible?

    Here is a small sample of what the syntax would look like:
    UPDATE Card SET CardNumber = '8740351' WHERE (CardNumber = '2795938');
    UPDATE Card SET CardNumber = '8269986' WHERE (CardNumber = '2277122');
    UPDATE Card SET CardNumber = '8139230' WHERE (CardNumber = '668459');
    UPDATE Card SET CardNumber = '5361736' WHERE (CardNumber = '6799637');
    UPDATE Card SET CardNumber = '6950434' WHERE (CardNumber = '7646305');
    UPDATE Card SET CardNumber = '9106492' WHERE (CardNumber = '8263976');
    UPDATE Card SET CardNumber = '7021708' WHERE (CardNumber = '1772307');
    UPDATE Card SET CardNumber = '5737028' WHERE (CardNumber = '2952859');
    UPDATE Card SET CardNumber = '9070496' WHERE (CardNumber = '4053968');
    UPDATE Card SET CardNumber = '1745478' WHERE (CardNumber = '5163525');

    These are partial SSN's that I am replacing with a random and unique number.

    I have approximately 900 updates that I would need to have performed on the Access data file. I used an Excel document to help build all of my UPDATEs.

    Any thoughts?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    not quite what you ask, but you could automate running them one by one from an imported or linked XLS.

    assuming table is called 'myLinkedXLS', and the UPDATE statement is in 'SQLfield', here is DAO-how:

    dim rst as DAO.recordset
    set rst = currentdb.openrecordset("SELECT SQLfield FROM myLinkedXLS")
    with rst
    do while not .eof
    currentdb.execute !SQLfield
    .movenext
    loop
    end with
    rst.close
    set rst = nothing

    DAO code so requires a reference to DAO library. ADO equivalent exists

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you have a CurrentNum and a NewNum field in Excel - copy paste the data into a new table and then I think you could get away with using a single update statement
    Code:
    UPDATE Card
     INNER
      JOIN MyTable
        ON MyTable.CurrentNum = Card.CurrentNum
    SET    Card.CurrentNum = MyTable.NewNum
    George
    Home | Blog

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    conceded George!

    an approach like yours is more elegant.

    not a major issue for a one-time run of a few hundred updates, but elegance is a goal in itself.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    You could just do an update with mid("abcdefgbf",4)& left("abcdefgbf",3)

    without using a randomizer or copy and paste
    returns defgbfabc
    Now is that a hack solution or what ?
    I had to say it
    Dale Houston, TX

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is that in the right thread? I have no idea what you just said Dale
    George
    Home | Blog

  7. #7
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    the user wanted to update a SS# field with a random number - instead of using a randomizer or creating a table with equal number of random numbers for the rows of ss#' and then running an update

    If the DBA was the only person using the code he/she could have used my code and it would have saved alot of time - but as I said it was a hack solution - right thread - bad advice
    Dale Houston, TX

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •