Update one column of a table with data from another table

Question

Post reply

Update one column of a table with data from another table

k-dub

SSC Veteran

Points: 255
More actions
July 27, 2008 at 5:44 am

#210583

I have two tables which are different with the exception of a single field (column name = "LocationCode").
Both tables can also be joined with a common id field.
I would like to update the "LocationCode" values in one table with the the "LocationCode" of the other table. I can do a relational join on the common id.
It is impractical for me to update one row at a time. How can I update all the rows quickly?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 1

UPDATE A

SET A.LocationCode = B.LocationCode

FROM tableA A INNER JOIN tableB B ON A.id = B.id

--Ramesh

999baz Ten Centuries Points: 1053 More actions · Answer 2

Hi Ramesh,

Does it matter how big the update is?

I wanna update 2 million + cloumns from one table to the other in a similar manner to your update statement. Is this the correct apprach for such qtys?

Varinder Sandhu Mr or Mrs. 500 Points: 559 More actions · Answer 3

Dear Friends,

if tableA from database X and TableB form database Y then how can handle this situations

Please reply anyone !!!

Varinder Sandhu,
http://www.varindersandhu.in/

Varinder Sandhu Mr or Mrs. 500 Points: 559 More actions · Answer 4

found the solutions...

actually if the tables lie in different dbs

then create temp table in db X that contain the data of tableB from db Y

this way our both tables now in same db as X

now simply we can update from one table to another as usual 🙂

Varinder Sandhu,
http://www.varindersandhu.in/

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Varinder Sandhu (9/18/2010)
found the solutions...
actually if the tables lie in different dbs
then create temp table in db X that contain the data of tableB from db Y
this way our both tables now in same db as X
now simply we can update from one table to another as usual 🙂

When u have data from different DBs, then accessing the tables using three part naming convention will help..

As in:

UPDATE tblA_dbX

SET tblA_dbX.Col2 = tblB_dbY.Col2

FROM dbX.dbo.TableA tblA_dbX -- See the 3-part naming here <DB_Name>.<Schema_Name>.<Table_Name>

JOIN dbY.dbo.TableB tblB_dbY -- See the 3-part naming here <DB_Name>.<Schema_Name>.<Table_Name>

ON tblA_dbX.Col1 = tblB_dbY.Col1

Or, am i missing something here ???

homebrew01 SSC Guru Points: 55548 More actions · Answer 6

ColdCoffee (9/18/2010)
Varinder Sandhu (9/18/2010)
found the solutions...
actually if the tables lie in different dbs
then create temp table in db X that contain the data of tableB from db Y
this way our both tables now in same db as X
now simply we can update from one table to another as usual 🙂
When u have data from different DBs, then accessing the tables using three part naming convention will help..
As in:
UPDATE tblA_dbX
SET tblA_dbX.Col2 = tblB_dbY.Col2
FROM dbX.dbo.TableA tblA_dbX -- See the 3-part naming here <DB_Name>.<Schema_Name>.<Table_Name>
JOIN dbY.dbo.TableB tblB_dbY -- See the 3-part naming here <DB_Name>.<Schema_Name>.<Table_Name>
ON tblA_dbX.Col1 = tblB_dbY.Col1
Or, am i missing something here ???

Sounds right to me. Creating a temp table is an extra step, uses extra space & resources.

Varinder Sandhu Mr or Mrs. 500 Points: 559 More actions · Answer 7

Dear Friends,

Yes i have checked your way also we can do update with this way...

temp table is temporary table after we should remove this.

Thanks!!!

Varinder Sandhu,
http://www.varindersandhu.in/

gofrancesc Hall of Fame Points: 3269 More actions · Answer 8

hi 999baz,

it depends, doing this update you will lock a big chunk of data.

First of all you must test it, use your development DB and test with 10000 rows, 100000 rows, or yours 2500000 rows, and estimate the time needed to complete the task.

Now you know the amount of time needed; have you an adequate time frame to do it without disturb the normal operation?. If yes you can schedule your update; if not you should update your table using a cursor and committing your changes after some amount of rows updated.

Hans K SSC Enthusiast Points: 181 More actions · Answer 9

Hi there, I am also trying to do something like this, but keep getting an error 42000.

My query looks like this:

UPDATE dbo.BlogCopy_copy

SET dbo.BlogCopy_copy.ProcID = dbo.BlogProcedures.ProcID

FROM dbo.BlogCopy_copy, dbo.BlogProcedures

Where dbo.BlogWebs_copy.WebID=dbo.BlogProcedures.WebID

The actual error is : [Err] 42000 - [SQL Server]The multi-part identifier "dbo.BlogWebs_copy.WebID" could not be bound.

I created a new table in BlogCopy_copy for ProcID and I need to have that populated with the same value as the "ProcID" in the table BlogProcedures.

Any help with this will be greatly appreciated.

Thanks

Hans

Van Heghe Eddy SSCarpal Tunnel Points: 4449 More actions · Answer 10

Hi,

Try this one

UPDATE bck
SET bck.ProcID = ori.ProcID
FROM dbo.BlogCopy_copy as bck inner join dbo.BlogProcedures as ori
on bck.WebID=ori.WebID

Using aliasses of tables is a good habit that you really should learn to use 🙂

Wkr,

Van Heghe Eddy

Hans K SSC Enthusiast Points: 181 More actions · Answer 11

Thanks for the tip - I did manage to find out why it did not work, I needed to use an additional "WHERE" qualifier.

I also agree with the alias suggestion, but my main platform for development is web based and I use DreamWeaver for that. Using aliases tends to mess with the built in SQL builder, so I have never really spent too much time getting to use aliases.

Thanks

Hans