473,581 Members | 3,213 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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.

Sep 20 '05 #1
5 1600
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´t 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´t need a single
update for every record exisiting.

HTH, Jens Suessmeyer.

Sep 20 '05 #2
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)
Sep 20 '05 #3
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_rFa ct.in_SPAM_fo> wrote in message
news:13******** *************** *********@4ax.c om...
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)

Sep 20 '05 #4
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 ConversionHelpT able
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 = ConversionHelpT able.NewValue
FROM table1
INNER JOIN ConversionHelpT able
ON ConversionHelpT able.OldValue = table1

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Sep 20 '05 #5
Hugo Kornelis (hugo@pe_NO_rFa ct.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, es****@sommarsk og.se

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

Sep 21 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
4339
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my predecessor, I hasten to add) so that each day it creates a copy of the record for each company, changes the date to today's date, and prompts the user...
33
4259
by: Lee C. | last post by:
I'm finding this to be extremely difficult to set up. I understand that Access won't manage the primary key and the cascade updates for a table. Fine. I tried changing the PK type to number and setting default value to a UDF that manages the auto-numbering. Access won't take a UDF as a default value. Okay, I'll use SQL WITHOUT any...
0
1921
by: Dalan | last post by:
I'm not certain if this is doable in the manner explained below, but I'm certain that there have been Access developers who have done something similar. So whatever insight and assistance that can be provided will be much appreciated. There is a separate Update Query (well several) that consolidate various goods from separate tables for...
8
6544
by: Nick M | last post by:
Hello All, Excellent info here Thanks! I am very new to using access in general and I am on a learning curve. I'm trying to import an excel workbook (with worksheets) into an access db via a macro. (I'll get to using VB later on). What I would like to do is import a single workbook w/three seperate worksheets into three seperate access...
4
2260
by: steroche | last post by:
I would REALLY appreciate help please please please! Im sure it is probably blindingly obvious to most of you but I am totally in the dark here!I am lost - i thought i had finally figured out this dataSet updating lark when i realised that i think i am right back at square 1!!! Here's my scenario - i have a SQLDB and i retrieve all my data...
15
1959
by: graham | last post by:
Hi all, <bitching and moaning section> I am asking for any help I can get here... I am at the end of my tether... I don;t consider myself a genius in any way whatsoever, but I do believe I have a logical way of thinking that has served me well during my years of application development with various tools... But having been working with...
12
2068
by: Janaka Perera | last post by:
Hi All, We have done a object oriented design for a system which will create a class multiply inherited by around 1000 small and medium sized classes. I would be greatful if you can help me with the following: Can this create any problems with GNU g++ compilation, linking etc? What would be the impact to the performance of the system?...
5
2257
by: Wiley | last post by:
I have created a form that contains 1000s of items, though I have an expression that have calculated the grand total. If I make any changes to my quantities the grand total does not change. Hence how do I use access to continually update and calculate the grand total in a form after each new entry.
16
3480
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate...
0
7788
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8139
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8299
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7890
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8166
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6546
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5667
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5357
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
1127
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.