update Statement in stored procedure

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)

update Statement in stored procedure

Author

Topic

santoshkale
Starting Member

1 Post

Posted - 2010-07-15 : 06:40:14

Hi,

I am having a problem with updat statement. In my update statements few rows does not get updated. I have joined update table with #temp table to update. Here is a sequence of statements i execute in stored procedure.

For example.
I have a stored procedure in which i have multiple statements.

Statement 1 - select data from multiple tables using joins and insert it into #temp table

statement 1
INSERT INTO #tmpTable
SELECT
t1.id
t2.name
FROM
table1 t1
INNER JOIN table2 t2 ON t1.ID = EP.t2ID
WHERE --some conditions

Statement 2 - updating a column in one of the above used table, i have joined this table with the above # temp table in my update statement.

UPDATE table1
SET flag = 1
FROM table1 t1
INNER JOIN #tmpInvTab tEP ON t1.Id=tEP.Id

statement 1 gets all matching rows but statement 2 does not update all rows fetched in statement 1 few rows are not updating the flag.

What could be the possible reason, also i want to know if statement 2 will get executed before statement 1 complets.

can any one help me with this.

Thanks for your help in advance.

Kristen
Test

22859 Posts

Posted - 2010-07-15 : 06:49:39

"statement 1 gets all matching rows but statement 2 does not update all rows fetched in statement 1 few rows are not updating the flag."

You'll be relieved to hear that this is not possible

so something else is spoiling your day


statement 1
INSERT INTO #tmpTable
SELECT 
t1.id
t2.name
FROM
table1 t1
INNER JOIN table2 t2 ON t1.ID = EP.t2ID
WHERE --some conditions

SELECT @@ROWCOUNT AS [Statement 1 Rowcount]

Statement 2 - updating a column in one of the above used table, i have joined this table with the above # temp table in my update statement.

UPDATE table1 
SET flag = 1
FROM table1 t1
INNER JOIN #tmpInvTab tEP ON t1.Id=tEP.Id

SELECT @@ROWCOUNT AS [Statement 2 Rowcount]

I trust you get the same answers? (assuming that no rows have been deleted from [table1] between the two statements)

I have assumed that for a give row in [Table1] there is only one corresponding row in [Table2] - otherwise I think rowcount-2 will be same, but perhaps not.

SELECT COUNT(DISTINCT [id]) FROM #tmpTable may be of interest if there are multiple rows in [Table2] for each row in [Table1]

other possibility is that something else, running at the same time, could change some rows (back to flag = 0) or a trigger on the table could be doing that or, in the case of a INSTEAD OF trigger, could even be denying individual rows from being updated.

Or, I suppose, [table1] is actually a View and updating it is only hitting certain rows.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources