update command for multiple columns on a single table using joins, taking more time to run

Question

0.00/5 (No votes)

See more:

Hi All,

I have written an Sql update for multiple columns on a single table by using joins.

Note:- Table is around 20,000,000 records and moreover my update command has to update around 1,000,000 records in that table based on condition. Please help me out in writing out my update command which runs faster.

--------
Table A
--------

i have total 30 columns in a table. Now i need to update 3 columns values for each row in a table with another two columns values of the same table. i.e Col15 value need to updated with Col2 and in simillar way Col15 with Col12. (Col2 = Col15 and Col12 = Col25 and Col4 = Col15)

note :- Col ---> Column in a table.

command i used is as below

SQL

update A
set
    A.RequestedAmount= B.GrossAmount,
    A.RequestedUnits = B.Units,
    A.EstimatedAmount = B.GrossAmount
from TUnit A
    join TUnit B
        on A.UID = B.UID
        and A.TID = B.TID
        and A.AID = B.AID
        and A.TAID = B.TAID
    join #TmpTUnit U
        on A.UID = U.UID
        and A.TID = U.TID
        and A.AID = U.AID
        and A.TAID = U.TAID
        and A.TType = U.TeType

-- #TmpTUnit U is another temp table which i got from select statement.
-- TUnit is my main table with 30 columns and have records around 20,000,000 records.

Please help me out the way i can update my main table.

Thanks & Regards
chiranjeeviO

Posted 21-Jul-13 15:42pm

ommi.chiru

Add a Solution

Comments

_Asif_ 21-Jul-13 23:46pm

Is your table partitioned?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

_Asif_ · Answer 1 · 2013-07-21T19:49:00

I think the fastest and safest way to do this can be elaborated in below steps

* Identify all rows (primary key) that will get affected and store it in excel sheet be executing below query

SQL

SELECT A.UID, B.GrossAmount, B.Units, B.GrossAmount
from TUnit A
    join TUnit B
        on A.UID = B.UID
        and A.TID = B.TID
        and A.AID = B.AID
        and A.TAID = B.TAID
    join #TmpTUnit U
        on A.UID = U.UID
        and A.TID = U.TID
        and A.AID = U.AID
        and A.TAID = U.TAID
        and A.TType = U.TeType

* transform your update query into single primary key based query. Since you now have 100K rows in the excel sheet you can build the update query very easily.

SQL

UPDATE TUNIT set RequestedAmount= some number, RequestedUnits = some number, EstimatedAmount = some number where UID = 12345;
UPDATE TUNIT set RequestedAmount= some number, RequestedUnits = some number, EstimatedAmount = some number where UID = 123468;
...

This is going to be fast as it will hit primary key index and use the active partition as well;