Update field from one database to another database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Update field from one database to another database

Author

Topic

pineappleflower
Starting Member

6 Posts

Posted - 2008-12-15 : 23:08:03

One of my table in my current database data is wrongly updated by user, I want to update it to correct value using my backup database, how can I do this?

For example:
TableA have field subject and ID(unique), I have installed both database in the same server, but my problem is how to write an update script to update the field from backup db to current db using ID as the key word.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-15 : 23:28:34

Something like this:

update sourcedb.dbo.tablename
set sourcedb.dbo.tablename.fieldname = backupdb.dbo.tablename.fieldname
from sourcedb..tablename
inner join backupdb..tablename
where sourcedb..tablename.id = backupdb...tablename.id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 00:02:31

i would prefer using aliases to prevent confusion with long names


update s
set s.fieldname = b.fieldname
from sourcedb.dbo.tablename s
inner join backupdb.dbo.tablename b
where s.id = b.id

pineappleflower
Starting Member

6 Posts

Posted - 2008-12-16 : 00:27:49

When I use the query below, I hit the following error, any ideas?

update s
set s.subject = b.subject
from [990].dbo.bacodiscussions s
inner join [999].dbo.bacodiscussions b
where s.hid = b.hid

Error:
=======
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'where'.

pineappleflower
Starting Member

6 Posts

Posted - 2008-12-16 : 00:32:22

I found the error, can not use WHERE clause, i use ON and it is work fine.

update [990].dbo.bacodiscussions
set [990].dbo.bacodiscussions.subject = [999].dbo.bacodiscussions.subject
from [990].dbo.bacodiscussions
inner join [999].dbo.bacodiscussions
on [990].dbo.bacodiscussions.hid = [999].dbo.bacodiscussions.hid

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-16 : 09:00:31

oops !! we both missed ON part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-16 : 10:42:51