BlakeMcKenna
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.
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.
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
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
select count(*)
from transactions
LEFT JOIN Accounts ON Transactions.acctName = Accounts.acctName
where Accounts.acctName IS NULL
ASKER
Oh, SQL Server.
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.
UPDATE dbo.Transactions
SET dbo.Transactions.acctID = dbo.Accounts.acctID
FROM dbo.Transactions
INNER JOIN dbo.Accounts
ON (dbo.Transactions.acctName = dbo.Accounts.acctName)
;
my bad I read "my SQL Server DB" as = MySQLWhat 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.
ASKER
I'm using SQL Server 2008...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool...thanks for the help Scott!
and even if tested there's a backup when applying to production (just 'in case').
is the existing acctName column involved in any constraints?