How to update 1000s of items in 50 tables

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.

Its hard to give youan exact answer without having more information, a
good point would be to start at the bottom, the master records, there
you could have two columns holding the old and the new values. If the
key changes going down to deeper granularity you have to do the same
(new / old column).

The tables itself should be at last updates with updating the old
column with tne new column and then dropping (if you don=B4t want to
retain the old information) the old.

Its not quite easy and should be planned carefully (and tested of
course, with only a subset of data). But then you won=B4t need a single
update for every record exisiting.

HTH, Jens Suessmeyer.

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)

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:13u0j15kev9puhb
 n2p0k6jibbcqsu0cs86@
4ax.com...
> On Tue, 20 Sep 2005 05:46:33 GMT, rdraider wrote:
> 
>
> 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)

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)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo)  writes:
> 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).


Also, one more important thing here. Don't do:

BEGIN TRANSACTION
go
UPDATE tbl
SET    ...

IF @@error <> 0
go
UPDATE nexttbl

That is, don't split up the job in several batches, because some errors
causes SQL Server to abort the batch and rollback the transaction. If you
have multiple batches, you could end up having run the latter half, while
the first was rolled back.

I would recommend that you put all this stuff in a stored procedure, rather
than just a script.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp