Update with two tables?
Your query does not work because you have no FROM clause that specifies the tables you are aliasing via A/B.
Please try using the following:
UPDATE A
SET A.NAME = B.NAME
FROM TableNameA A, TableNameB B
WHERE A.ID = B.ID
Personally I prefer to use more explicit join syntax for clarity i.e.
UPDATE A
SET A.NAME = B.NAME
FROM TableNameA A
INNER JOIN TableName B ON
A.ID = B.ID
How to update multiple tables with single query
Here is an example using the output
clause:
declare @ids table (id int);
update table1
set status = 1
output inserted.id into @ids
where status = 2;
update table2
set status = 1,
date = getdate()
where personid in (select id from @ids);
update multiple table using one query in SQL SERVER
You can't. Update statement works for a single table. You have to write three different queries for three tables.
You can use transaction to make sure that your update statements are atomic.
BEGIN TRANSACTION
UPDATE Table1
Set Field1 = '1';
Where Field = 'value';
UPDATE Table2
Set Field1= '2'
Where Field = 'value';
UPDATE Table3
Set Field1= '3'
Where Field = 'value';
COMMIT
For C# you can use SqlTransaction. An example from the same link (bit modified)
private static void ExecuteSqlTransaction(string connectionString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
SqlTransaction transaction;
// Start a local transaction.
transaction = connection.BeginTransaction("SampleTransaction");
// Must assign both transaction object and connection
// to Command object for a pending local transaction
command.Connection = connection;
command.Transaction = transaction;
try
{
command.CommandText =
"UPDATE Table1 Set Field1 = '1' Where Field = 'value';";
command.ExecuteNonQuery();
command.CommandText =
"UPDATE Table2 Set Field1= '2' Where Field = 'value'";
command.ExecuteNonQuery();
command.CommandText =
"UPDATE Table3 Set Field1= '3' Where Field = 'value'";
command.ExecuteNonQuery();
// Attempt to commit the transaction.
transaction.Commit();
Console.WriteLine("Both records are written to database.");
}
catch (Exception ex)
{
Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
Console.WriteLine(" Message: {0}", ex.Message);
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
Console.WriteLine(" Message: {0}", ex2.Message);
}
}
}
}
Update multiple tables in SQL Server using INNER JOIN
You can't update more that one table in a single statement, however the error message you get is because of the aliases, you could try this :
BEGIN TRANSACTION
update A
set A.ORG_NAME = @ORG_NAME
from table1 A inner join table2 B
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID
update B
set B.REF_NAME = @REF_NAME
from table2 B inner join table1 A
on B.ORG_ID = A.ORG_ID
and A.ORG_ID = @ORG_ID
COMMIT
Related Topics
Len Function on Float in SQLserver Gives Wrong Length
How to Connect a Localdb from Visual Studio to the Published Azure Web App
Get Sundays for a Given Month Date in a Function SQL
Callablestatement + Registeroutparameter + Multiple Row Result
Compare Deleted and Inserted Table in SQL Server 2008
SQL Trigger Cannot Do Instead of Delete But Is Required for Ntext, Image Columns
Differencebetween Cross Join and Multiple Tables in One From
Insert or Select Strategy to Always Return a Row
When How to Use an Identifier Number Instead of Its Name in Postgresql
Using Table Just After Creating It: Object Does Not Exist
Transpose Rows to Columns Based on Id Column
Order Guarantee for Identity Assignment in Multi-Row Insert in SQL Server
SQL - Unequal Left Join Bigquery
Can SQL Clr Triggers Do This? or Is There a Better Way
How to Group by Each Day in Pl/Sql