SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Import/Export (DTS) and Replication (2000)
 Update field from one database to another database
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pineappleflower
Starting Member

Malaysia
6 Posts

Posted - 12/15/2008 :  23:08:03  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/15/2008 :  23:28:34  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/16/2008 :  00:02:31  Show Profile  Reply with Quote
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
Go to Top of Page

pineappleflower
Starting Member

Malaysia
6 Posts

Posted - 12/16/2008 :  00:27:49  Show Profile  Reply with Quote
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'.

Go to Top of Page

pineappleflower
Starting Member

Malaysia
6 Posts

Posted - 12/16/2008 :  00:32:22  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/16/2008 :  09:00:31  Show Profile  Reply with Quote
oops !! we both missed ON part.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 12/16/2008 :  10:42:51  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

oops !! we both missed ON part.


yeah..
Go to Top of Page

pineappleflower
Starting Member

Malaysia
6 Posts

Posted - 12/16/2008 :  21:21:49  Show Profile  Reply with Quote
Thanks guys
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000