| Author |
 Topic  |
|
|
pineappleflower
Starting Member
Malaysia
6 Posts |
 Posted - 12/15/2008 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/15/2008 : 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
India
47154 Posts |
Posted - 12/16/2008 : 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
Malaysia
6 Posts |
Posted - 12/16/2008 : 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
Malaysia
6 Posts |
Posted - 12/16/2008 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 12/16/2008 : 09:00:31
|
| oops !! we both missed ON part. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47154 Posts |
Posted - 12/16/2008 : 10:42:51
|
quote:
Originally posted by sodeep
oops !! we both missed ON part.
yeah..  |
|
|
|
pineappleflower
Starting Member
Malaysia
6 Posts |
Posted - 12/16/2008 : 21:21:49
|
| Thanks guys |
|
|
| |
Topic |
|
Update field from one database to another database
