Update tables in 2 different databases
-
Hello All
I am trying to update one table with data from another table that is in a different database. Can I perform an "UPDATE" from one database into another? Or am I going about this in an incorrect manner? Any advise would be greatly approciated. Here is the code that I am using:
UPDATE TableName1
SET databaseName1.ColumnName1 = TableName2.ColumnName2
FROM TableName2
WHERE TableName2.ColumnName2 = TableName1.ColumnName1
Basically this is the actual code. Each table has other data that I am not updating or moving. I only want to take data from one column, and update a table.column in a different SQL database. Both databases are on the same server.
UPDATE Cleints
SET StartDate = Employees.StartDate
FROM Employees
WHERE Employee.StartDate = Client.StartDate
Thank You in advance
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on. -
Problem I see here is it looks at startdate = startdate to decide to update startdate to startdate which for them to meet must already be so. Is this right and if not can you tell me how the tables are equal without the value to be set being it. Then try something like this.
UPDATE Clients
SET Clients.StartDate = Employees.StartDate
FROM
Clients C1
RIGHT JOIN
Employees
ON
C1.IDVal = Employees.IDVal AND
C1.StartDate != Employees.StartDate
I think I put that down right but to test to be sure.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Thanks for your reply
I am not sure what the '!' is about in the last command that you suggested in your posting to my question. C1.StartDate != Employees.StartDate Please clearify this one. Thanks 🙂 I am testing the suggestion that you posted right now. Any assistnace with this will be greatly appreciated.
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on. -
!= means does not equal, SO I am checking to make sure the value is not alreadys set, and if is we will not update as is not needed.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
I am sorry but this is not working.
I need to be able to update data that is in "Client" database with data that is in "Employee" database. I have tried INSERT statements, UPDATE statements. I am getting errors "SQL Server does not exist or access is denied" at this time. Both databases are on the same Server. I have ran the sp_addlinkedserver with the parameter of the First database.
I can use DTS to export it. But I need to create a Trigger or a Stored Procedure to make this happen on the fly.
Any assistance would be greatly appreciated
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on. -
Sorry try this, sometimes it is hard to logically think about something when other things are going on but this should be it.
UPDATE Clients
SET Clients.StartDate = Employees.StartDate
FROM
Employees
WHERE
Clients.IDVal = Employees.IDVal AND
Clients.StartDate != Employees.StartDate
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
-
Firstly you should remove the linked server via sp_dropserver. As the databases are on the same server you don't need to worry about linking servers.
I think your problem is that you need to qualify the database and owner names in your query.
EG:
UPDATE db1.owner.table1
SET db1.owner.table1.field1 = db2.owner.table2.field1
FROM db2.owner.table2
WHERE db2.owner.table2.field2 = db1.owner.table1.field2
Hope this helps some.
Phill
Edited by - phillcart on 05/05/2002 11:48:46 PM
--------------------
Colt 45 - the original point and click interface
-
Try this way by alaising.....
UPDATE a
SET field1 = b.field1
FROM db1.owner.table1 a, db2.owner.table2 b
WHERE b.field2 = a.field2
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply