An old database that has been in use for many years has finally reached that stage where it needs to be retired and substituted for one more suited to the needs of the business it is being used in. Where possible the new database has been designed to make the eventual transfer of existing data from the old to the new database as painless as possible. There are however a couple of tables where I will need to perform some updates once the initial data transfer (or at least all the easy bit) has taken place.
Basic update statements in SQL I'm familiar with, but iterating through an entire table I've not done before and I'm not sure of the most efficient way to go about it.
By way of an example of what I would like to do. Suppose that have Table A that now has several more fields than it's original counterpart. Many of these addition fields will contain information that cab be derived from other tables in the database. So table A now has a field which needs to contain the information contained in two fields in Table B. I would like to go through every row in table a and set field x to be equal to the concatenation of fields c & d from table b where the identity field of table b is equal to the value in field y of table a. In pseudo sql something like,
For each row in table a set field x = (select field c + field d from table b where id = field y) next row
There are roughly 8500 rows to run through. Each row has a valid entry in field y and table b has id's in its id field to match those, so really this is just a case of the most efficient type of sql statement to do this type of thing that I'm after.
Thanks for any suggestions that you might have.