Posted by Hugo Kornelis on 09/21/05 01:36

On Tue, 20 Sep 2005 22:04:30 GMT, rdraider wrote:

>Hugu,
>This database is part of an application. There are no foreign key
>constraints and many tables don't even have a primary key. I don't think I
>can modify the design of the tables without breaking the app. The
>application vendor is of no help.

Hi rdraider,

In that case, you'll have to bite the bullet and use a list of update
statements. One for each table. Do enclose all update statements in a
transaction and include error handling, so that you don't end up with
the new value in half of your tables changed and the old value in the
other half. (Instead of enclosing all in a transaction, you could take a
backup before the conversion starts and restore if something goes
wrong).

>What if I create update statements to cover all tables and fields (not hard
>to do with Excel) then just pass the old and new items as parameters? Is
>this possible?

That's possible, but not very efficient, as the same table has to be
passed over several times (once for each pair of old value/new value).

> If the old and new items were in a separate table, how do I
>pass those to the update statements?

That would be much more efficient. You'd have to set up a table that
holds old value and new value for each possible value of the column (if
a value won;t change, include it with old value equal to new value).
Then the query to change a column in one table would be:

UPDATE table1
SET column1 = (SELECT NewValue
FROM ConversionHelpTable
WHERE OldValue = table1.column1)

If only some of the values need to change and many others remain
unchanged, then it might be more efficient to have only the values that
actually need to be changed in the helper table and change the SQL to

UPDATE table1
SET column1 = ConversionHelpTable.NewValue
FROM table1
INNER JOIN ConversionHelpTable
ON ConversionHelpTable.OldValue = table1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]