One update statement - 2 messages with # rows affected

Question

Post reply

One update statement - 2 messages with # rows affected

Bill Maners

Ten Centuries

Points: 1340
More actions
February 27, 2009 at 7:48 am

#206347

The following update statement:
Update company_ext_attributes
set ExtValue603 = AprimoRTSMappings.Region,
ExtValue604 = AprimoRTSMappings.Territory,
ExtValue312 = AprimoRTSMappings.SubCode
from AprimoRTSMappings,companies,company_ext_attributes
where companies.company_id=company_ext_attributes.company_id
and company_ext_attributes.ExtValue301 IN ('OED','EID')
and companies.country=AprimoRTSMappings.country
and ISNULL(AprimoRTSMappings.state,'A') =
Case
when companies.country <> 'USA' then ISNULL(AprimoRTSMappings.state,'A')
else companies.state
end
AND companies.company_id in (797039,846066,926631,819017,825779,916530,874446,835550)
returns:
(1 row(s) affected)
(4 row(s) affected)
Whereas the 'select' version this query only returns 4 rows.
What can the first (1 row affected) be?
Thanks for the help,
Bill

Viewing 8 posts - 1 through 7 (of 7 total)

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

Mike John SSCertifiable Points: 7216 More actions · Answer 1

I think you have probably turned on "include actual execution plkan" in SSMS

When on it returns an extra result set .. OR there is a trigger in there that fires and updates one row.

Mike

Bill Maners Ten Centuries Points: 1340 More actions · Answer 2

When I turn on Include Actual Execution Plan, I get;

(4 row(s) affected)

(1 row(s) affected)

There is an update trigger:

But is suggests all 4 rows should be updated leading to 2 messages of (4 row(s) affected) each.

Bill

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Check triggers on company_ext_attributes

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Bill Maners Ten Centuries Points: 1340 More actions · Answer 4

Here is the trigger. When I reviewed this, I concluded that each of the 4 rows would have been updated, therefore the additional message would have 4 affected rather that 1 affected.

I had intended to include the trigger in the previous reply - sorry.

ALTER Trigger [dbo].[AGS_InsertUpdateCompanyExtAttributes] ON [dbo].[company_ext_attributes]

AFTER INSERT, UPDATE

AS

BEGIN

Declare @SFDC_AcctOwnerID nvarchar(400)

, @SFDC_AccountOwnerName nvarchar(255)

, @CompanyID int

IF @@RowCount > 0

BEGIN

SELECT @CompanyID = company_id FROM inserted

if (@CompanyID is null)

SELECT @CompanyID = company_id FROM updated

--Grab the SFDC Account Owner ID (represents a SFDC Login ID)

SELECT @SFDC_AcctOwnerID = ExtValue227

FROM company_ext_attributes

WHERE company_id = @CompanyID

--Grab the Participant name that is tied to a SFDC User, via the Partipant's Owner Key (EA ID: )

SELECT @SFDC_AccountOwnerName = RTRIM(LTRIM(isNull(first_name + ' ', '') + isNull(last_name,'')))

FROM participant_ext_attributes pea

INNER JOIN participants p

ON p.participant_id = pea.participant_id

WHERE pea.ExtValue901 = @SFDC_AcctOwnerID

UPDATE company_ext_attributes

SET ExtValue239 = @SFDC_AccountOwnerName

WHERE company_id = @CompanyID

END

Mike John SSCertifiable Points: 7216 More actions · Answer 5

That trigger will not work correctly with multiple rows. Common mistake of retrieving something from inserted into a variable - so you get one of them. There are lots iof examples around on how to write triggers that handle multi-row updates, but essentially you need to ensure ALL rows in inserted and deleted are dealt with (preferably not using a cursor!)

Mike

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

SELECT @CompanyID = company_id FROM updated

Your code contains the line above... there's no such thing as an UPDATED table inside the trigger. Do you have a table outside the trigger called UPDATED?

Trigger tables are named INSERTED and DELETED... there is no UPDATED trigger table.

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

beezell Hall of Fame Points: 3112 More actions · Answer 7

Very true. To see what rows are updated you refer to the DELETED table for the old values and INSERTED for the new values.

Cheers,

Brian