SQL standard tools -- mass substitutions

[tom kroto]

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.

[Paul Squires]

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

[Michael Mattias]

ODBC returns "number of rows affected" in response to an UPDATE or DELETE statement, so I am sure SQL Tools does, too.

[tom kroto]

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.

[Paul Squires]

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.

[tom kroto]

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.

[Michael Mattias]

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