Announcement

Collapse
No announcement yet.

SQL standard tools -- mass substitutions

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

  • SQL standard tools -- mass substitutions

    I have SQL standard tools. I want to do a mass substitution for a particular field in an access database. There is this sample program --sql-upd.bas. It does an update to one record. But I want to make the same substitution to many records in the table.

    Several possibilities have entered my mind. Since I'm not quite sure how SQL tools works, here are some ideas:
    just loop back to the point where the SQL statement is submitted?
    Do an SQL fetch and then loop back?


    I could experiment and waste a lot of time. Better to ask the question.
    Sample code would be appreciated. Thank you.

  • #2
    You could do a simple UPDATE sql statement.... just leave off the WHERE clause so that all rows in the table are updated.
    http://www.w3schools.com/SQl/sql_update.asp
    Paul Squires
    FireFly Visual Designer (for PowerBASIC Windows 10+)
    Version 3 now available.
    http://www.planetsquires.com

    Comment


    • #3
      ODBC returns "number of rows affected" in response to an UPDATE or DELETE statement, so I am sure SQL Tools does, too.
      Michael Mattias
      Tal Systems (retired)
      Port Washington WI USA
      [email protected]
      http://www.talsystems.com

      Comment


      • #4
        multiple replace not mass replace

        I didn't phrase my question properly. Out of 500 records let say 100 records contain the same value in one field. I want to change the value in these 100 records, not all 500. What you told me is valuable for future reference. Thank you.

        Comment


        • #5
          Out of 500 records let say 100 records contain the same value in one field.
          Well, in that case, just use the WHERE condition to identify those 100 records with the specific value that you want to change to the new value. Maybe something like the following:

          UPDATE Customers SET ZipCode=90210 WHERE ZipCode=90111

          That will change all records in the table where ZipCode is equal to 90111 to a value of 90210.
          Paul Squires
          FireFly Visual Designer (for PowerBASIC Windows 10+)
          Version 3 now available.
          http://www.planetsquires.com

          Comment


          • #6
            surprised at result

            Paul,
            Yes it worked as you said. I am surprised that SQL would take over reading the whole file and making changes on its own throughout the file. I would have thought that I would have to drive it through each record myself. Thanks for info which has changed my perspective of SQL as a tool.

            Comment


            • #7
              I am surprised that SQL would take over reading the whole file and making changes on its own throughout the file
              This is kind of semantic, but it's not 'SQL' , 'SQL Tools' or even ODBC which 'does' anything. For a DBMS to claim it supports Structured Query Language commands, there is a certain defined minimum command set it must support. UPDATE... WHERE is one of those 'minimum requirements'

              If you have gone thru the SQL materials on the w3shools web site, you can appreciate why so many designers will select database management systems which DO support SQL.

              MCM
              Michael Mattias
              Tal Systems (retired)
              Port Washington WI USA
              [email protected]
              http://www.talsystems.com

              Comment

              Working...
              X