Update query

  • Thread starter Thread starter Hemang Shah
  • Start date Start date
H

Hemang Shah

Hello Is it possible to update data in multiple tables in one query ?

If not, is it possible to update in one table but for which the where clause
has conditions on Joins and other table?

Thanks
 
Sure. A single CommandText can contain as many UPDATE or other action
commands as you want. It can also call a SP to do all of the work (which is
typically how it's done in more sophisticated applications).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
As long as the database supports batch commands then yes you can send
multiple commands to it. As far as the second question though - I'm not
sure I understand what you mean but I'll take a stab at it. Are you asking
if you can send an Update command to the database with a subquery/join that
references a different table? If so, then yes, you can do that as well and
AFAIK that's supported with any database that supports joins/subqueries.
 
Because in these systems, you rarely have the luxury of updating a single
table. More often than not you have to execute an operation that rolls
changes through a number of tables where the process might encompass a
half-dozen tables in queries, changes, inserts, deletes and logging. The SP
can be hundreds of lines long (or more often call a dozen smaller SPs).
These systems were never able to use the simplistic Update method created
for the simple one-table designs. DBAs (for the most part) don't even grant
direct table access for updates so these SPs have to be granted specific
permission to change these tables.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I'd agree in most scenarios, and about the DBA not giving direct table
access - that's a DBA versus Developer issue rather than a technology issue.

However even within a stored procedure you will find yourself writing a
number of queries and tying them together within one transaction or the like
of it. So how is that any better than issuing multiple queries from the API
and tying them in using SqlTransaction instead?

In other words, writing complex long stored procedures almost seems like the
anti-thesis of keeping your actual logic outside the database. So what is
the compelling argument of keeping "sophisticated application logic" inside
the stored procedure?

In fact, I can think of a number of reasons why that would be a bad idea.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------


William (Bill) Vaughn said:
Because in these systems, you rarely have the luxury of updating a single
table. More often than not you have to execute an operation that rolls
changes through a number of tables where the process might encompass a
half-dozen tables in queries, changes, inserts, deletes and logging. The SP
can be hundreds of lines long (or more often call a dozen smaller SPs).
These systems were never able to use the simplistic Update method created
for the simple one-table designs. DBAs (for the most part) don't even grant
direct table access for updates so these SPs have to be granted specific
permission to change these tables.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sahil Malik said:
Bill,

I'm curious why you say more sophisticated applications tend to do this in
stored procs.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------
--
 
Yes, it's a matter of opinion. Many IT and development managers want to
maintain more system-based routines that everyone must use instead of
relegating/delegating the authority to the deployed application. Some
developers would rather perform these operations on their own. I'm of the
opinion that letting a connected client manage a transaction is ... well,
dangerous. If a transaction is started and the client goes to lunch...

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
Well, nothing wrong with managing transactions out of the API instead of the
database, because that doesn't necessarily mean that the transactions are
left open uncommitted (as you said gone for lunch). I believe that problem
can be solved by limiting the scope of the transaction and an architecture
that ensures calling Rollback when in doubt or otherwise.

But my personal propensity is to wrap transactions in the API instead of the
database. The only two exceptions to this rule IMO are -

a) When wrapping such a transaction causes a chatty communication between db
and app server, or too much data being ferried, or C# ends up doing what
TSQL is better at doing.

or

b) You are *emulating* a distributed transaction when using linked tables or
databases. But even when I'm using BEGIN DISTRIBUTED TRANSACTION, I'd still
prefer to wrap that in System.Transactions instead of Beg. Dis. Tran.

And there are other reasons around business objects and design patterns
where I would prefer to wrap transactions in the API, not the database.

So essentially Managers and DBAs who restrict access to tables because they
(think) they know better, should be fired.

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
 
Back
Top