Update Several records with same ID? How do they d

Results 1 to 5 of 5

Thread: Update Several records with same ID? How do they d

  1. #1
    Patty Guest

    Default Update Several records with same ID? How do they d

    How do I update several records that have the same ID? example:<BR><BR>table1 - fields: Id, ColorValue<BR>1 23<BR>1 7<BR>1 6<BR>1 27<BR>2 8<BR>2 7<BR>2 3<BR><BR>How do I change the values for ID #1 to:<BR>1 24<BR>1 1<BR>1 7<BR>1 26<BR><BR>Would it be easier for me to delete all records that belong to ID#1 and Insert the new values?<BR><BR>Thanks<BR>Patty<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Update Several records with same ID? How do th

    I think you&#039;ll have to update each record individually...<BR><BR>eg. UPDATE table1 SET ColorValue=24 WHERE Id=1 AND ColorValue=23<BR>UPDATE table1 SET ColorValue=1 WHERE Id=1 AND ColorValue=7<BR>UPDATE table1 SET ColorValue=7 WHERE Id=1 AND ColorValue=6<BR>UPDATE table1 SET ColorValue=26 WHERE Id=1 AND ColorValue=27<BR><BR>A better way (perhaps) to do it would be to have unique ids for each record, that way you could shorten the SQL statement, but as far as I&#039;m aware, unless you&#039;re performing a simple conversion (eg +1, -1, *4, /4, etc...) you&#039;ll have to do each one individually... I don&#039;t know of any way to do a lookup on an update... Doesn&#039;t mean there isn&#039;t a way though :)<BR><BR>Chris

  3. #3
    Patty Guest

    Default Delete/Insert is the best way...isn't it ?

    The problem is that I don&#039;t want to replace one specific value with another one. All I want to do is "replace" all of the existing values with new values.<BR><BR>I guess a 1)delete then 2)Inset would work best in a situation like this. But would the delete/insert process increase the size of my MsAccess DB? I know when it deletes, it doesn&#039;t compact the DB automatically...or does it???

  4. #4
    Join Date
    Dec 1969

    Default RE: Delete/Insert is the best way...isn't it

    i have the same problem. i too think that delete/insert is the best option. but i also think there are some complex sql statements that can do our work . we need to learn them.

  5. #5
    Join Date
    Dec 1969

    Default Will you always have same count...

    ...of records with ID=1??<BR><BR>What happens if you have 4 records (your example) before, but only 3 records afterwards? Or 5 afterwards?<BR><BR>If you might not have the same count, then I would say there is no doubt that delete/insert is the *only* way to go.<BR><BR>*IF* you can guarantee that the count won&#039;t change, then you *COULD* do a sequence of updates. Kind of ugly, but:<BR><BR>&#060;%<BR>newColorValues = Array( 24, 1, 7, 26 )<BR>count = UBound(newColorValues)<BR><BR>Set RS = Server.CreateObject( "ADODB.RecordSet" )<BR>RS.Open "SELECT * FROM table WHERE id = 1", conn, adOpenKeySet, adLockPessimistic<BR><BR>If RS.RecordCount &#060;&#062; count+1 Then<BR>&nbsp; &nbsp; Response.Write "Counts don&#039;t match...can&#039;t do this!"<BR>&nbsp; &nbsp; Response.End<BR>End If<BR><BR>For i = 0 To UBound( newColorValues )<BR>&nbsp; &nbsp; RS("colorValue") = newColorValues(i)<BR>&nbsp; &nbsp; RS.Update<BR>&nbsp; &nbsp; RS.MoveNext<BR>Next<BR><BR>RS.Close<BR>%&#062;<BR> <BR>But is that *really* any better than delete/insert? I doubt it. And the .mdb file doesn&#039;t grow forever, you know. It does compact itself every so often, when it makes logical sense (because of blocks that get filled up, etc.).<BR><BR>

Posting Permissions

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