更新使用SQL Server一个查询多个表 [英] update multiple table using one query in SQL SERVER

问题描述

我的工作asp.net（C＃）项目与SQL Server 2008中我想用一个查询来更新三个表。请建议我该怎么做。 thnaks

I am working on asp.net(c#) project with SQL SERVER 2008. I want to update three tables using one query. Please suggest me how to do that. thnaks

推荐答案

您不能。 Update语句适用于单个表。你必须写三个不同的查询三个表。

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

对于C＃，您可以使用的SqlTransaction 。从相同的链接的例子（位修改）

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);
            }
        }
    }
}

这篇关于更新使用SQL Server一个查询多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！