Execute an Update and Select at the same time?

I'm trying to update a table in my SQL Server DB. I'm trying to do it in the simplest way possible but not sure of the syntax or if it's possible for what I want to do. Here is what I have. Visually, the columns being updated don't match but I'm changing the column name and type once the data is converted.

    UPDATE Transactions B
          SET B.acctName = (SELECT acctID FROM Accounts WHERE acctName = B.acctName)

This is what I'm trying to accomplish but the syntax is rejected.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi, hope this is being done in a test environment first?

and even if tested there's a backup when applying to production (just 'in case').

is the existing acctName column involved in any constraints?
BlakeMcKennaAuthor Commented:
It's all done in test and there is a backup of the DB and the acctName is not involved in any constraints.
I would suggest then that you add a new column, of the correct data type to the Transactions table (with the wanted field name).

Then I would update that new column:

UPDATE Transactions
INNER JOIN Accounts ON Transactions.acctName = Accounts.acctName
SET Transactions.????? = Accounts.acctID

Then follow-up by adding the new column to indexes as needed, and eventually dropping the unwanted column Transactions.acctName
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

btw: you may also want to do something like this for verification

select count(*)
from transactions
LEFT JOIN Accounts ON Transactions.acctName = Accounts.acctName
where Accounts.acctName IS NULL
BlakeMcKennaAuthor Commented:

I tried your syntax and got an error. See screenshot!
Oh, SQL Server.
UPDATE dbo.Transactions 
SET dbo.Transactions.acctID = dbo.Accounts.acctID
FROM dbo.Transactions 
    INNER JOIN dbo.Accounts 
    ON (dbo.Transactions.acctName = dbo.Accounts.acctName)

Open in new window

my bad I read "my SQL Server DB" as = MySQL

What version of SQL Server?

and this question isn't related to these either is it?

Tags: vb.net
Topic: Microsoft Visual Basic.Net

I'll fix these tags/topics later.
BlakeMcKennaAuthor Commented:
I'm using SQL Server 2008...
Scott PletcherSenior DBACommented:
You had the statement essentially right.  It's just that an alias can't appear directly after the UPDATE keyword, you need to use the FROM clause to assign the alias:

UPDATE Transactions
          SET B.acctName = (SELECT acctID FROM Accounts WHERE acctName = B.acctName)
FROM Transactions B

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BlakeMcKennaAuthor Commented:
Cool...thanks for the help Scott!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.