April 26, 2009 at 8:24 am
I need to update 40 fields in table1 (400,000 records) using data from table1.
I am using SQL server 2005
Nearly every column in table1 is dependent on another column.
If I know the value of Field3, then I can calculate the value of Field4.
If I know the value of Field4, then I can calculate the value of Field5.
……….
If I know the value of Field39, then I can calculate the value of Field40.
This is what I am doing now:
e.g. (the update statements have been simplified)
First I insert data into field1 and field2, and then I run the following update statements:
UPDATE table1 SET Field3=Field2 *2
UPDATE table1 SET Field4=Field3 *6
UPDATE table1 SET Field5=Field4 *10
UPDATE table1 SET Field6=Field5 +2
…….
UPDATE table1 SET Field40=Field39 +7
I was wondering if there is a more efficient way to perform the updates.
Thanks in advance for you advice.
April 26, 2009 at 8:36 am
Hello,
Could you consider using Computed Columns in this table, or are the values not always dependant?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
April 26, 2009 at 9:58 am
Computed column are not allowed to be used in another computed-column
E.g.
create table dbo.Table1
(
Field3 money NOT NULL,
Field4 money NOT NULL,
Field5 AS Field4 *2 PERSISTED,
Field6 AS Field5 *2 PERSISTED
)
I get this error message if i run the above statement:
Msg 1759, Level 16, State 0, Line 1
Computed column 'Field5' in table 'Table1' is not allowed to be used in another computed-column definition.
April 26, 2009 at 10:17 am
Hello again,
From the sample that you originally provided it seemed that all the columns could be calculated from the “base columns”, but may be that was just because you had simplified the calculations for publication?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
April 26, 2009 at 10:25 am
Hi John,
Thanks for your post.
The update statements are much more complicated than in this simple example.
They involve 2 -10 lines of code.
February 17, 2010 at 2:36 am
Hi.
Either use a CTE to do the computation and then output the rows, or create one or more temporary tables, or table variables and update these in turn before outputing the final rows.
Regards
Roy
February 17, 2010 at 4:07 am
landrior (4/26/2009)
I was wondering if there is a more efficient way to perform the updates.
If you really need to store (persist) all the values, the best you can do is probably to rewrite the UPDATE as a single statement, expanding the definitions as you go:
UPDATE dbo.table1
SET Field3 = Field2 * 2,
Field4 = (Field2 * 2) * 6,
Field5 = ((Field2 * 2) * 6) * 10,
Field6 = (((Field2 * 2) * 6) * 10) + 2...
If you are updating many indexed columns, it might be worth considering dropping those indexes before the UPDATE, and re-creating them afterward. If you can share more information about the problem, you'll probably get a more specific solution.
Paul
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply