CODEWITHSUNDEEP
asp.netsql-serverc#-4.0database
Puneet Purohit
Puneet Purohit

Reputation: 1291

update multiple table using one query in SQL SERVER

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

Upvotes: 2

Views: 6763

Answers (3)

Habib
Habib

Reputation: 223422

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

Upvotes: 5

शेखर
शेखर

Reputation: 17604

One of the way will be create a procedure and inside that procedure update all the table. You can also use transaction if you want to insert all the three or non of them. Here is an example

You can also maintain transaction from C#. here is an example Transaction Stored Procedure C#
Example

using (var connection = new SqlConnection("your connectionstring"))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
    try
    {
        using (var command1 = new SqlCommand("SP1Name", connection, transaction))
        {
            command1.ExecuteNonQuery();
        }
        transaction.Commit();
    }
    catch
    {
        transaction.Rollback();
        throw;
    }
  }
}

Upvotes: 1

Tim M.
Tim M.

Reputation: 54417

Write three queries and put them all into a single transaction, either in a stored procedure or using TransactionScope from your c# code.

using System.Transactions;

using( var ts = new TransactionScope() ){

  // execute your queries

  ts.Complete();
}

Full example here: http://msdn.microsoft.com/en-us/library/system.transactions.transactionscope.aspx

Upvotes: 2

Related Questions