Nested Update Statement (NEW)

Question

Nested Update Statement (NEW)

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 1

Matjaz Justin (10/17/2007)
create a view and instead of update trigger on that view.
lp, Matjaž

Creating the view is fine.... but it won't help you update multiple base tables with a single update. You will get an error unless it's a correctly partitioned view. It won't work on a joined-table view.

And, can you actually put an update trigger as you state on a view in 2k5? I don't think so... but I could be wrong.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 2

Sandy (10/16/2007)
Hey Grant & Jeff,
I think you both are concentrating multiple table updates
rather than Nested Update Statement,
I clearly mentioned that I have used Nested Update statement
rather than Multiple Table Updates,
And more over to Grant, Update statement will not allow more than one table, which is known to every one in SQL Server.
What I focus here, In One Update,
I am trying to update another table
but with a same condition,
And Jeff, Please have a look on to my Topic heading.
Cheers!
Sandy

But, that's what we're trying to tell you... whether you call it a "nested update" or not, you cannot update two separate tables with a single condition like what you are trying to do. They must be two separate updates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

The only way to "emulate" what you seem to be getting at would be to wrap the two independent updates into a single transaction, so that if one fails, they both fail (and can be reversed, or Rolled back as the official terminology would have it). Still - like Jeff said - no matter how you slice it - they're written and treated as two separate operations

Something like

BEGIN TRANSACTION

Update tb1

set blah1=newvalue1

where

etc...

update tb2

set blah2=newvalue2

where

etc...

COMMIT TRANSACTION

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

noeld SSC Guru Points: 96590 More actions · Answer 4

And, can you actually put an update trigger as you state on a view in 2k5? I don't think so... but I could be wrong.

Yes you are. You can even do that in 2000. (INSTEAD OF TRIGGERs can be setup in views) 😎

* Noel

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 5

Thanks, Noel... that's why I like this place... I learn something new everyday 🙂 I'll check out BOL on that. Thanks again.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

noeld SSC Guru Points: 96590 More actions · Answer 6

Jeff Moden (10/17/2007)
Thanks, Noel... that's why I like this place... I learn something new everyday 🙂 I'll check out BOL on that. Thanks again.

Count me in. That makes us Two 😀

* Noel

Matjaz Justin Mr or Mrs. 500 Points: 598 More actions · Answer 7

Example:

create view v_tb2_tb1

as

select t1.id, t1.name, t2.Salary

from Tb1 t1 INNER JOIN Tb2 t2 on

t1.id = t2.id

go

create trigger tiofu_vtb2tb1 on v_tb2_tb1 instead of update

as

set nocount on

update t2 set salary = i.salary

from tb2 t2 join inserted i on

i.id = t2.id

where i.salary <> t2.salary

update t1 set name = i.name

from tb1 t1 join inserted i on

i.id = t1.id

where i.name <> t1.name

go

update v_tb2_tb1 set name = 'ccc', salary = 5000

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

Hey Matjaz Justin,

Great post,

its really helpful in the future,

but don't you think its more Complex way rather than

using simple updates on multiple tables by using transaction,

like this

=======

Begin Tran

Update Tb1

Set

Update Tb2

Set

Commit

-------

More over to your post, you are using Trigger which may not a good practise in SQL Server if your DB is Huge, If I am not wrong. and Trigger on View,

I think we have to think more about the standard rather than achieving the task,

Please Correct me, If I am Wrong.

Cheers!

Sandy.

--