Web Analytics Made Easy -
StatCounter update multiple records - CodingForum

Announcement

Collapse
No announcement yet.

update multiple records

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • update multiple records

    I tried something like:
    Code:
    .execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id=1;UPDATE tbl1 SET fld1="& val1 &" WHERE id=2;UPDATE tbl1 SET fld1="& val1 &" WHERE id=N;")
    And it didn't work.
    Any other idea (without writing '.execute' N times).

    Thanks

  • #2
    .execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id In (1,2,...)")

    the ... of course mean 'and othr id's' --> they should not be in the actual statemnts. A list of other id's should be there instead.

    You can dynamically build the In-collection (for isntance when loopin through the formfield)-collection and then do a.

    UPDATE tbl1 SET fld1="& val1 &" WHERE id In (" & coll & ")
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

    Comment


    • #3
      Thanks
      I'm getting closer...
      But my problem is that fld1's value (val1) is changing between each ID...
      If I had to do it manually, it'd be:
      Code:
      .execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id=1;")
      .execute ("UPDATE tbl1 SET fld1="& val2 &" WHERE id=2;")
      .execute ("UPDATE tbl1 SET fld1="& valN &" WHERE id=N;")
      It seems like only the ID's are changing in your query, and I need to change both ID's and val's.

      Thanks for further help

      Comment


      • #4
        Originally posted by ShMiL
        It seems like only the ID's are changing in your query, and I need to change both ID's and val's.
        Seems like in your query, it were also only the id's that were changing:
        Originally posted by ShMiL
        .execute ("UPDATE tbl1 SET fld1="& val1 &" WHERE id=1;UPDATE tbl1 SET fld1="& val1 &" WHERE id=2;UPDATE tbl1 SET fld1="& val1 &" WHERE id=N;")
        If the values differ, then this is a complete other operation, and then you'll need to either do it one record at a time, or do a recordset update.

        You can still do it dynamically. Run a search here (quickest way to a problem) and you'll find plenty of threads where i and others have posted examplecode.
        Like
        http://www.codingforum.net/showthrea...ht=update+loop
        http://www.codingforum.net/showthrea...ht=update+loop
        http://www.codingforum.net/showthrea...ht=update+loop
        Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

        Comment


        • #5
          I looked at the links...
          And what I understand from it, is that I can't do it without doing multiple .execute's
          (http://www.codingforum.net/showthrea...ht=update+loop --> raf)

          Am I right?

          Comment


          • #6
            No. You can not. All possible options are explained inside that link.

            You can not update one field if you have differnt values for the records you need to update. You can however reduce the number of updates, by grouping the records, based on the new value for the variable). You then have 1 update for all records that need to get the same value for the variable. Like

            update table set var1 = " & value1 & " where id In(" & collection1 & ")
            update table set var1 = " & value2 & " where id In(" & collection2 & ")
            update table set var1 = " & value3 & " where id In(" & collection3 & ")

            The collection are then build like explained in the links i posted.
            But this is only useful if the number of records with the same new value is high enough.

            Else just use the 1 record at a time update.
            Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

            Comment


            • #7
              Not only they are not high enough, they are unique!
              ok,
              I'll do multiple queries.

              Thank you verymuch!

              Comment


              • #8
                You're welcome.
                Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                Comment


                • #9
                  Why don't you use a loop?

                  Comment


                  • #10
                    Originally posted by zzg
                    Why don't you use a loop?
                    Good idea
                    Maybe check the links and you'll see that the code in each link uses a 'for each'-loop to iterate through the formscollection.
                    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html

                    Comment

                    Working...
                    X