Replace a list of item codes in .dbf file

Posted on 2010-01-06
Last Modified: 2013-11-24
I have a .dbf database table where I have a list of item codes in a column KITNO that need to be replaced with new codes.
I am using Borland Database Desktop v7 to make changes, using the sql editor.

to replace 1 itemcode at a time I would use

update kits
set kitno = "newcode"
where kitno = "oldcode"

What I would like to do though, is replace a whole list of "newcode" with corresponding "oldcode". There is far too many to want to do one at a time :)

Any suggestions on how to do this would be appreciated.
Question by:robd2007
    LVL 7

    Expert Comment

    Hi, if you have the new values in a table, why don't you use a while not eof loop to run an edit statement foreach record in the table ?

    Author Comment

    Thanks for the reply, that sounds great as I have a table with new and old.

    Could you give me a hand with the syntax? ;)
    LVL 7

    Accepted Solution

    Procedure TForm1.UpdateDB;
      with mytable do
         while not eof do
             UpdateKits(FieldByName('newcode').AsString, FieldByName('oldcode').AsString);

    procedure TForm1.UpdateKits(aNewCode, aOldCode: String);
     here you update the kits table by running your SQL

    Author Closing Comment

    Thank you very much, this saves me a lot of manual labour :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    Viewers will learn how to maximize accessibility options in an Excel workbook for users with accessibility issues.
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now