Sql and loops problem

Results 1 to 3 of 3

Thread: Sql and loops problem

  1. #1
    Join Date
    Dec 1969

    Default Sql and loops problem

    I have a database from which I need to remove any incomplete records before an action is done with the data otherwise conflicts will occur. So before anything is done with the data i need to remove any incomplete records. Also once a record is deleted I need to reassigna number in each of the other records to keep them all in increments of 1 (to prevent another conflict)<BR><BR>What I have tried to do is run the delete sequence for each record that meets the criteria to be deleted in a while loop which also contains a for loop to reassign the numbers of all the higher records by subtracting 1 from them (up to the total number of records). If there are up to 3 records incomplete it seems to work ok but with any more, sometimes it will break out of the while loop and seems to have loop problems too if incomplete records are scattered throughout the table.<BR><BR>Would I be better to delete all incomplete records first then run another script to put the remaining records in sequence? effectively separating the tasks mentioned above (It is not suitable to have default database values for everything)

  2. #2
    Join Date
    Dec 1969

    Default By the sounds of it...

    ...you need to loop through the whole (or nearly the whole) table to update all records&#039; ID numbers. So, why don&#039;t you do that - loop through the whole table, and as you do so, keep a counter for the ID number. Then, when you find a record that should be deleted, do so, and go to the next one. Then update the next record with your current counter and continue:<BR><BR>1. Open table<BR>2. Go to first record<BR>3. Set counter = 1<BR>4. If record to be deleted - delete record<BR>5. Otherwise - update ID of current record from counter and increase counter by one<BR>6. Go to next record<BR>7. Go to step 4 if not at end of table<BR><BR>Oliver.

  3. #3
    Join Date
    Dec 1969
    Indianapolis, IN

    Default RE: Sql and loops problem

    This all sounds like a bad database design. Why don&#039;t you use an Primary Key with an Identity (auto number)? You wouldn&#039;t have to resort anything. Just delete files you don&#039;t want. Period. Any new records added would be given a unique ID. My advice is to get out of this current mindset of the database. It will save you more headaches in trying to think through and manage a bad database.

Posting Permissions

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