asp.net multiple table update statement

Question

I need to turn this query into an update statement. I will have to update the values from fields. Everything is already in place but the update statement.

Here is the select version of the query:

SELECT i.GoLiveDate, i.FirstBonusRun, i.TechFName, i.TechLName, i.TechEmail, i.TechPhone, i.WebISPFName, i.WebISPLName, 
              i.WebISPEmail, i.WebISPPhone, i.FullFillFName, i.FullFillLName, i.FullFillEmail, i.FullFillPhone, d.FName,
              d.LName, d.HomePhone, d.Email
              FROM NC_Information i
              INNER JOIN Distributor d
                ON d.DistID = i.ClientID
              WHERE clientID = @value

Is it possible to update two different tables from within the same query?

Here is the code I have so far:

public void Update (int ClientID)
{
    using ( var conn = new SqlConnection( GeneralFunctions.GetConnectionString() ) )
    using ( var cmd = conn.CreateCommand() )
    {
        conn.Open();
        cmd.CommandText =
        @"SELECT i.GoLiveDate, i.FirstBonusRun, i.TechFName, i.TechLName, i.TechEmail, i.TechPhone, i.WebISPFName, i.WebISPLName, 
          i.WebISPEmail, i.WebISPPhone, i.FullFillFName, i.FullFillLName, i.FullFillEmail, i.FullFillPhone, d.FName,
          d.LName, d.HomePhone, d.Email
          FROM NC_Information i
          INNER JOIN Distributor d
            ON d.DistID = i.ClientID
          WHERE clientID = @value";
        cmd.Parameters.AddWithValue( "@value", ClientID );
        cmd.ExecuteNonQuery();
    }
}

Nope just update one table put statement. An update statement begins with update tablename. — paparazzo
– paparazzo, Commented Apr 23, 2012 at 18:01

James Johnson · Accepted Answer · 2012-04-23 18:04:02Z

3

You can't update multiple tables in one statement, but you can use a transaction to make sure that the updates are contingent upon one another:

BEGIN TRANSACTION

UPDATE SomeTable
SET SomeColumn  = 'Foo' 
WHERE SomeID = 123   

UPDATE AnotherTable
SET AnotherColumn = 'Bar'
WHERE AnotherID = 456    

COMMIT

answered Apr 23, 2012 at 18:04

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Wilson Over a year ago

I'm going to try and implement this one. I like them being contingent upon one another.

Pankaj · Accepted Answer · 2012-04-23 18:11:32Z

I think, you cannot directly do the update on two tables. But you can Optimize the query.

How?

OUTPUT keyword in Insert/Update/Delete Statement

The first Update Statement's Select Data(filtered data) can be reused using the below mentioned example.

CREATE TABLE #table1
(
    id INT,
    employee VARCHAR(32)
)
go

INSERT INTO #table1 VALUES 
      (1, 'name1')
     ,(2, 'name2')
     ,(3, 'name3')
     ,(4, 'name4');
GO

DECLARE @GuestTable TABLE
(
    id INT,
    employee VARCHAR(32)
);

update #table1
Set id = 33
OUTPUT inserted.* INTO @GuestTable
Where id = 3

The Data in the '@GuestTable' Table is filtered data and can be reused.

select * from @GuestTable

drop table #table1

ssis_ssiSucks · Accepted Answer · 2012-04-23 18:17:52Z

0

Alternatively, you can create a dataset with two datatables, and let the tableadaptermanager manage the updates.

answered Apr 23, 2012 at 18:17

ssis_ssiSucks

1,5061 gold badge12 silver badges11 bronze badges

Collectives™ on Stack Overflow

asp.net multiple table update statement

3 Answers 3

1 Comment

How?

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

How?

Comments

Comments

Related