Updating values in one table from another table

Question

Post reply

Updating values in one table from another table

Govind-607836

Grasshopper

Points: 19
More actions
January 14, 2008 at 4:24 pm

#378016

HI All!
how can I update a column(Zipcode) values of a tableA from tableB without creating any new table in SQL 2000?
PS: Both the tables have more than 10MM records with each table containing 25 fields.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 1

What's are the common columns between the two tables? Gotta have something to join on to start with.

Recommend you post the schema for both tables...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 2

You need to join your one table with another in your WHERE clause.

Charlie-551146 Say Hey Kid Points: 677 More actions · Answer 3

You could try a statement like this.

Update tableA set

zipcode = b.zipcode

from

tableB b

Inner join

tableA a

ON

b.zipcode = a.zipcode

Where

[Add reason for update condition here]

[font="Arial"]Thank You,
[/font]
[font="Comic Sans MS"]Charlie[/font]

[font="Arial"]Charles Eaton[/font]

Govind-607836 Grasshopper Points: 19 More actions · Answer 4

Thanks Charlie! Yes, ofcourse this works.. But, here am looking to move the things fast.. There are 56 columns each in both the tables and I need to update 8 of them..

Any suggestion??

Thanks!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Well - considering you haven't provided any details of what you're trying to do - it will be hard to make any assessment as to performance.

I'd be worried about trying to do this update in one single activity, since that would tend to lock things up pretty good with 10 Million records to be updated.

You could use a technique often referred to as "walking the index" to run multiple smaller updates to cover all of the records. You can then have a lower impact on shared resources like RAM, tempdb, etc... since you will be reusing the same space over and over. The best performance will come if you walk the CLUSTERED index, so that you're updating all rows in a given page at the same time (as opposed to having to visit each data page multiple times).

The example assumes you have an integer identity field, but you could use the same idea against an alpha scan as well (using a beginning letter and ending letter range for each chunk).

declare @tempID as int

declare @chunksize as int

declare @maxid as int

select @tempid=min(id),

@chunksize=50000,

@maxid=mad(id) from MyTableToUpdate

While @tempID=<@maxid

begin

Update MyTableToUpdate

set a=MyUpdates.a, etc... --in other words all of your updates

from

MyTableToUpdate

Inner Join MyUpdates on MyTableToUpdate.joinid=MyTableToUpdate.joinid

Where --add in whatever other conditions you might need

MyTableToUpdate.id BETWEEN @tempid and (@tempid+@chunksize)

--increment @tempid

select @tempid=@tempid+@chunksize+1

End

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 6

Spot on, Matt.

I'll also add that SQL Server seems to have "tipping points" depending on the size of the table being updated, etc. What I mean by that is that it may take only a minute (for example) or two to update a million rows... it will likely only take twice that to update two million rows... but, it could take anywhere from several hours to several days to update 10 or 20 million rows. Not sure what the "tipping point" is or how to calculate what it might be other than doing sample runs that sneak up on the tipping point, though.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 7

Govind (1/15/2008)
Thanks Charlie! Yes, ofcourse this works.. But, here am looking to move the things fast.. There are 56 columns each in both the tables and I need to update 8 of them..
Any suggestion??
Thanks!

Yes... to echo Matt's sentiment and my previous request...

...please slow down and give us the details we asked for so we can help you... what will the join columns be, what is the table structure of both tables, what columns do you want updated, what is the structure of the primary key/indexes/foreign keys, and how many rows in each of the two tables?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)