Posted by rdraider on 09/21/05 01:04

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.
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? If the old and new items were in a separate table, how do I
pass those to the update statements?

Thanks

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:13u0j15kev9puhbn2p0k6jibbcqsu0cs86@4ax.com...
> 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]