Solved

MSSQL 2000 Update column (multiple records) based on select from another table

Posted on 2009-03-30
3
504 Views
Last Modified: 2013-11-15
Ok as dumb as this might sound, I forgot how to update multiple records in a single table based on a select from another table.

For example I have a table I backed up and made changes to a live table and therefor they are the same structure and lets say I made a mistake and would like to revert back the changed data prior (assuming I did not do a begin tran), I would like perform an update and compare the id number to be the same in the live table as the backed up table. I know this code I am writing below is not a working peice of work but it was something i thought in my head to get started with.


UPDATE mytable

SET mycol = (select mycol from myothertable where active = 'y' and currentrevision = 'y')

where mytable.myid = myothertable.myid

Open in new window

0
Comment
Question by:smyers051972
3 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 300 total points
ID: 24020668
You can do it two ways:

UPDATE mytable
SET mycol = (select mycol from myothertable where active = 'y' and currentrevision = 'y' and mytable.myid = myothertable.myid)

or

UPDATE mt
SET mycol = mot.mycol
from mytable mt
join myothertable mot
on mytable.myid = myothertable.myid
where mot.active = 'y'
and mot.currentrevision = 'y'
0
 
LVL 15

Assisted Solution

by:MNelson831
MNelson831 earned 200 total points
ID: 24020673
Update MyTable
Set MyField = OtherTable.MyField
From MyTable inner join OtherTable on MyTable.UniqueID = OtherTable.UniqueID
0
 
LVL 1

Author Closing Comment

by:smyers051972
ID: 31564413
Thanks!
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now