troubleshooting Question

Update a number of records using a select top statement

Avatar of netadmin2004
netadmin2004Flag for United States of America asked on
Databases
1 Comment1 Solution1789 ViewsLast Modified:
Hi guys,

I need 2 things.

First I have a table where I need to grab around 2000 records from the account table that are assigned to a certain rep and reassign them.

My thoughts (though wrong) are somthing like this:
UPDATE    ACCOUNT
SET              ACCOUNTMANAGERID = 'U6UJ9A000016'
WHERE     (SELECT TOP 2000 ACCOUNTMANAGERID
                       FROM          ACCOUNT
                       WHERE      (ACCOUNTMANAGERID = 'U6UJ9A000020'))

Second, I need to now assign the records I just reassigned and update the contact table to reflect those changes.

So I'm guessing something like the following:?
UPDATE    c
SET              accountmanagerid = 'U6UJ9A000016'
FROM         Contact c JOIN
                      Account a ON c.AccountID = a.AccountID AND a.accountmanager = 'U6UJ9A000016'

Any thoughts?
ASKER CERTIFIED SOLUTION
andygarratt

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 1 Comment.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros