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.
| Author |
Topic |
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
 Posted - 2004-11-08 : 08:30:16
|
I have an update query.update users set source=70 where source=60 BUT I want to do this for only 36 of the records that source is 60.How can I do this? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-08 : 08:33:14
|
| set rowcount 36update users set source=70 where source=60set rowcount 0update users set source=70 where pk in (select top 36 pk from users where source=60)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-11-08 : 08:49:06
|
| help!I did the second one update users set source=70 where pk in (select top 36 pk from users where source=60)and if said 250 records affected. Anyway to undo??? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-11-08 : 09:58:07
|
transaction log...Go with the flow & have fun! Else fight the flow  |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-11-08 : 10:01:35
|
| please help I'm new to sql.How do I get it from the transaction log. I have never used it before |
|
|
|
esthera
Master Smack Fu Yak Hacker
1410 Posts |
Posted - 2004-11-08 : 12:21:25
|
| can someone please help me. I ran an update statement and It was wrong.If I can recover using the transaction log then please tell me how. Otherwise I'm goign to have to get a backup and lose a weeks worth of data.Please help. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-08 : 13:29:56
|
| You should always test an update like this inside a transaction and only commit if it looks ok (I often take a copy of the rows I am going to update).If you are truncating the log (simple recovery mode?) or not taking log backups then you are stuck.If you have log backups then you could restore to a point in time on another server and copy the data.A weekly backup is a long time between backups - couldn't you do dailies or at least a diff every day.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-11-08 : 23:15:47
|
quote:
Originally posted by esthera
can someone please help me. I ran an update statement and It was wrong.If I can recover using the transaction log then please tell me how. Otherwise I'm goign to have to get a backup and lose a weeks worth of data.Please help.
check www.apexsql.com, download their log file reader--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-08 : 23:41:09
|
| Here's how I would go about it:Check your Recovery model (Enterprise Manager : Right click database : Options)If SIMPLE then stop - you are hosed :-(If FULL then:1) Make a transaction backup2) Make a full backup (so you can at least restore back to this point if needs be)3) Restore from your last full backup (not the one at Step-2 !!) + the transaction backup (i.e. at Step 1) and set a "point in time" to restore to - i.e. just before the "accident"Enterprise Manager is pretty good for (3) - it gives you a visual tree of the backup/transaction filesKristen |
|
|
|
|
|
|
update query help