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?
Scott PletcherConnect With a Mentor Senior 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
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.
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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
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...
BlakeMcKennaAuthor Commented:
Cool...thanks for the help Scott!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.