0

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.

Dom Sinclair
  • 2,458
  • 1
  • 30
  • 47

2 Answers2

0

Unless I'm missing something in your question it sounds like you want to do a simple joined update which can be done as a set based operation, so there is no need to use loops. If this is the case then this query should be what you want:

UPDATE a
SET a.x = CONCAT(b.c, b.d) -- possibly CONCAT(b.c, ' ', b.d) if you want a separator
FROM TableA a
JOIN TableB b ON a.y = b.id
jpw
  • 44,361
  • 6
  • 66
  • 86
0

Given the conditions you described I suggest using a set based Update, from my experience it would be the fastest one. I go with set based operations whenever possible as opposed to using a cursor or some iteration loop like a while.

You can take a look at this discussion regarding set-based operations vs processing rows individually.

Community
  • 1
  • 1
Alioza
  • 1,690
  • 12
  • 14