Nested Update Statement (NEW)

Question

Post reply

Nested Update Statement (NEW)

IN_Sandeep

SSCertifiable

Points: 6259
More actions
October 16, 2007 at 12:03 am

#182848

Hi All,
I just wanted to update different columns
from multiple Table.
I execute the below script,
its working fine,
Please correct me, If this is Wrong.
SQL SCRIPT
=========
CREATE TABLE #Tb1
(
id int,
name varchar(20)
)
---
CREATE TABLE #Tb2
(
id int,
Salary int
)
----
INSERT INTO #Tb1
SELECT 1, 'aaa'
UNION
SELECT 2, 'bbb'
-------
INSERT INTO #Tb2
SELECT 1, 1000
UNION
SELECT 2, 2000
UNION
SELECT 3, 3000
--------
SELECT * FROM #tb1
SELECT * FROM #tb2
------
DROP TABLE #Tb1
DROP TABLE #Tb2
-------
UPDATE #Tb1
SET name = 'ccc'
UPDATE #Tb2
SET salary = 5000
FROM
#Tb1 t1 INNER JOIN #Tb2 t2
ON t1.id = t2.id
-----------
Waiting for Feedback.
Cheers!
Sandy.
--

Viewing 15 posts - 1 through 15 (of 22 total)

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

hi,

I need the feedback for the Above Topic

from below SQL Gurus,

1. Steve Jones

2. Grant Fritchey

3. Andras Belokosztolszki

4. John Mitchell

Cheers!

Sandy.

--

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

Perfect... the rest of us can ignore you then.

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

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 3

Hey Jeff Moden,

I didn’t mean that

I apology if you mind it,

I just specify my favorite members in this forum,

Now I am also adding you as my favorite member,

Now happy.....:P

& wats abut my Topic feedback only Perfect......??

have a nice day,

Cheers!

Sandy

--

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 4

I wish I had real wit, but all I can come up with is "Hey, Steve, you've got a groupie."

Seriously though Sandy, you left out Gail Shaw, NinjaRGR and a whole slew of others, much more qualified than I am to answer the question (including Jeff Moden).

It looks like you're trying to update two tables at once. As far as I know, you can only do one table at a time. I even tried using a common table expression (CTE) to define the join first and then update that, but I got an error that I'm updating multiple base tables. You'll need to break down the updates and joins and do them twice to arrive at updates to only the matching rows from the two tables.

Unless someone else has something better?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

Grant is correct... you can update only 1 table at a time... you will need two totally 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)

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 6

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

--

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 7

Sandy,

I guess this is what you require...

UPDATE t2

SET t2.salary = 5000

FROM

#Tb1 t1 INNER JOIN #Tb2 t2

ON t1.id = t2.id

--Ramesh

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 8

nope,

I was trying to use the nested Updates statement

in SQL Server.

Cheers!

Sandy.

--

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 9

Sandy

I must admit I'm not clear what you're trying to do. You say in your first post that it's working fine... so what's the problem? Perhaps you could provide an expected result set given your sample data. Incidentally, the sample data doesn't make sense to me. Your query won't work unless you populate the ID columns of your tables, either manually or with the IDENTITY property. And why do you have three salaries but only two names?

John

Matjaz Justin Mr or Mrs. 500 Points: 568 More actions · Answer 10

create a view and instead of update trigger on that view.

lp, Matjaž

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 11

Hi All, (John, Matjaz, Grant, Jeff, Ramesh)

I agree with you all,

I was wrong, because I was thinking the

Query below is a Single Query,

UPDATE #Tb1

SET name = 'ccc'

UPDATE #Tb2

SET salary = 5000

FROM

#Tb1 t1 INNER JOIN #Tb2 t2

ON t1.id = t2.id

But actually when i executed by using Execution Plan, I came to know that it is not a single Query, It is 2 separated query.

1st

====

UPDATE #Tb1

SET name = 'ccc'

2nd

=====

UPDATE #Tb2

SET salary = 5000

FROM

#Tb1 t1 INNER JOIN #Tb2 t2

ON t1.id = t2.id

and the misconception was the nesting of one update with another,

I think John Clear me most,

Thanks Grant also for guiding me in a proper way,

Cheers!

Sandy.

--

Bob Fazio SSChampion Points: 10794 More actions · Answer 12

I must be missing something here. How are we updating tables that were deleted.

CREATE TABLE #x(a INT)

INSERT INTO #x(a) VALUES (10)

DROP TABLE #x

UPDATE #x

SET a=20

That doesn't work for me... :unsure:

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 13

Yes, I noticed that too. I assumed that Sandy was using it for testing and forgot to comment it out or delete it before posting the code.

John

Grant Fritchey SSC Guru Points: 398713 More actions · Answer 14

I apologize, but I've never heard of the nested update statement before. I ran it through Google and BOL. I'm not seeing it anywhere.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning