Update statement get executed but table remains the same

Question

Post reply

Update statement get executed but table remains the same

mr_adeelalisyed

SSC-Addicted

Points: 470
More actions
November 14, 2009 at 7:40 am

#217623

Hello Experts,
I’m trying to fill in one of my table with the information which I’m pulling from two different places (sys.Object & Information.schema.columns). So far I’ve fetch and stored all the data what I need. My first table structure is as follows
Create Table #TempTotalTable(
DBname varchar(100),
TBId int,
TBName varchar(500),
CapturedDate datetime,
TBCreationData datetime,
TBLastModifiedDate datetime,
NoOfCol int
)
And second table structure is like this.
create table #ColumnPerTable(
DBName varchar(100),
TBName varchar(500),
NoOfColumn int
)
Now keeping in mind at some point I would’ve to update NoOfCol in #TempTotalTable though #ColumnPerTable I kept two fields same on both tables TBName & NoOfColumn. Now my problem is when I execute the following update statement
update #TempTotalTable
set NoOfCol = NoOfCol
from #ColumnPerTable
where #TempTotalTable.TBName = #ColumnPerTable.TBName
It get executed with the message 235 rows get effected. However when I go back and do select on #TempTotalTable still NoOfCol shows null as follows.
DBName TBIdTBName CapturedDate TBCreationDate TBLastMoifiedDate NoOfCol
Master 326292222 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817NULL
Master 662293419 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817NULL
Master 710293590 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817NULL
I’m not able to understand why? Can anybody see what’s the problem here?
Thanks a lot in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Gabriel Briviesca SSC Enthusiast Points: 132 More actions · Answer 1

looking on the update statement

update #TempTotalTable

set NoOfCol = NoOfCol

from #ColumnPerTable

where #TempTotalTable.TBName = #ColumnPerTable.TBName

you might want to prefix the NoOfCol with #ColumnPerTable, i think you are setting the column with its own value.

La paz sea contigo,

Gabriel Bribiesca

LutzM SSC Guru Points: 107049 More actions · Answer 2

SQL Server does exactly what you told to do:

update #TempTotalTable

set NoOfCol = NoOfCol

from #ColumnPerTable

where #TempTotalTable.TBName = #ColumnPerTable.TBName

This basically means to update NoOfCol with its original values, resulting in a unchanged values.

Please note that it is "sometimes" important to qualify column names ("sometimes" from my point of view would apply at the minimum whenever there is a join of any kind).

If you want to update NoOfCol in table #TempTotalTable with values from NoOfCol from #ColumnPerTable, you need to tell it:

update #TempTotalTable

set #TempTotalTable.NoOfCol = #ColumnPerTable.NoOfCol

from #ColumnPerTable

where #TempTotalTable.TBName = #ColumnPerTable.TBName

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mr_adeelalisyed SSC-Addicted Points: 470 More actions · Answer 3

Thank you so much guy for your replies to solve my problem and I learned from my mistake.

Many Thanks again for showing me correct way.

YOU ALL ARE BEST.