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. 5 1595
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.
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:13********************************@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)
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, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
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...
|
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...
|
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...
| |
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...
|
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...
|
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...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |