Link to home
Start Free TrialLog in
Avatar of BlakeMcKenna
BlakeMcKennaFlag for United States of America

asked on

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.
Avatar of PortletPaul
PortletPaul
Flag of Australia image

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?
Avatar of BlakeMcKenna

ASKER

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
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
Paul,

I tried your syntax and got an error. See screenshot!
Screenshot.jpg
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.
I'm using SQL Server 2008...
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Cool...thanks for the help Scott!