Posted by Hugo Kornelis on 09/21/05 00:03

On Tue, 20 Sep 2005 05:46:33 GMT, rdraider wrote:

>Hi all,
>I'm looking for a way to re-number inventory items. The items exist in 50+
>tables, hundreds of fields and there are several thousand items. Maybe one
>table could hold the list of old & new items, another table holds the list
>of tables/fields to update? How can this be done without needing a million
>individual update statements?
>
>A brute force method may look like:
>update table1 set field1 = newitem where field1 = olditem
>
>update table1 set field2 = newitem where field2 = olditem
>
>update table2 set field1 = newitem where field1 = olditem
>
>Thanks for any input.
>
>

Hi rdraider,

If you have all the foreign key constraints in place, and you have
created them with the cascading updates option, it's actually as simple
as

UPDATE MainTable
SET Column = New value
WHERE Column = Old value

The cascading updates will make sure that the change is rolled out to
all other tables.

Since you have many tables, this might take some time. During that time,
all locks takes will make concurrent use of the database nigh on
impossible, so plan to do this during down time.

Also, make sure that your transacion log is big enough to hold all
changes. Grow it manually before starting if needed. Autogrow is quite
slow, since it allocates only a bit of extra disk space at a time.

Best, Hugo
--

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

[Back to original message]