Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Update one column of a table with data from another table Expand / Collapse
Author
Message
Posted Sunday, July 27, 2008 5:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 3:03 AM
Points: 1, Visits: 25
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?
Post #541520
Posted Sunday, July 27, 2008 6:13 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 5:52 AM
Points: 2,551, Visits: 2,594
UPDATE A
SET A.LocationCode = B.LocationCode
FROM tableA A INNER JOIN tableB B ON A.id = B.id



--Ramesh

Post #541521
Posted Tuesday, October 20, 2009 6:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 10:54 PM
Points: 41, Visits: 259
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?
Post #806209
Posted Saturday, September 18, 2010 3:56 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 21, 2011 8:08 AM
Points: 15, Visits: 35
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/
Post #988712
Posted Saturday, September 18, 2010 4:42 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 21, 2011 8:08 AM
Points: 15, Visits: 35
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/
Post #988718
Posted Saturday, September 18, 2010 5:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 12, 2014 5:21 PM
Points: 2,262, Visits: 5,406
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 ???
Post #988721
Posted Sunday, September 19, 2010 5:50 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:48 PM
Points: 2,832, Visits: 8,507
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.



Post #988895
Posted Sunday, September 19, 2010 11:17 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, May 21, 2011 8:08 AM
Points: 15, Visits: 35
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/
Post #989111
Posted Tuesday, September 21, 2010 2:57 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, May 8, 2014 6:52 AM
Points: 634, Visits: 809

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.
Post #990058
Posted Tuesday, March 19, 2013 12:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 9:38 PM
Points: 3, Visits: 17
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

Post #1432494
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse