Simple Question on Update Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Simple Question on Update Statement

Author

Topic

kcaxd39
Starting Member

4 Posts

Posted - 2008-03-04 : 11:58:26

When I execute following SQL Query Analyzer (SQL 2005) i am getting message 3 times "95 Records effected" in query analyzer message window.

UPDATE dbo.Load_AC_Install
SET Load_AC_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (Load_AC_Install JOIN Load_AC_Install_Rejected
ON Load_AC_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND Load_AC_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )

After I load "Load_AC_Install" table into Temporary table #Load_Install and When I execute following SQL Query from SQL Query Analyzer using temporary table I am getting message Only 1 time "95 Records effected"

Update #Load_Install
SET #Load_Install.Rejected_Reason = Load_AC_Install_Rejected.Validation_Reason
FROM (#Load_Install JOIN Load_AC_Install_Rejected
ON #Load_Install.Hardware_Portfolio_Barcode = Load_AC_Install_Rejected.Hardware_Portfolio_Barcode
AND #Load_Install.Install_Model_Barcode = Load_AC_Install_Rejected.Model_BarCode )

So My Question is if i use phicical table (dbo.Load_AC_Install) then i get message 3 times, but If i use same data with tempoary table(#load_Install) I get message only one time "95 Records Effected" in query analyzer message window!

Anyone know why?

Thanks for response and please ask me if you still have any questions!

DavidD
Yak Posting Veteran

73 Posts

Posted - 2008-03-05 : 00:29:56

Do you have any triggers on dbo.Load_AC_Install?

kcaxd39
Starting Member

4 Posts

Posted - 2008-03-05 : 18:13:51

Yes, I can see there is trigger created for dbo.Load_AC_install table.

Never worked/create trigger before... So you think becuase of trigger i am getting messages 3 times "95 Records Effected"?

Please advise!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 18:26:49

Script the trigger out...it is most likely that the trigger has an ON UPDATE action tied to it that affects each record update (and sounds like the trigger does TWO things to those same records)..

Poor planning on your part does not constitute an emergency on my part.

kcaxd39
Starting Member

4 Posts

Posted - 2008-03-06 : 16:51:16

Thank you so much all of you!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources