July 29, 2008 at 1:30 am
I have been given a task of an already existing [developed by someone who's left! 🙁 ] stored procedure
There is an update statement in this old SP:
"
UPDATE #temptable1
SET a.col1 = b.col1,
a.col2 = b.col2,
...
FROM #temptable1 a
RIGHT OUTER JOIN #temptable2 b
ON a.col3=b.col3
AND a.col4=b.col4
...
WHERE a.col5 <> 0
...
"
Where both temp tables are created in the beginning of SP.
This Stored Procedure obviously does not compile giving error: "Update/Delete from a table which is the inner table of an outerjoin is not allowed. Command aborted."
Trying to fix it…… 🙁
anybody has any idea pl?
July 29, 2008 at 1:40 am
UPDATE a --<--- I changed this!!!
SET a.col1 = b.col1,
a.col2 = b.col2
FROM #temptable1 a
RIGHT OUTER JOIN #temptable2 b ON a.col3=b.col3
AND a.col4=b.col4
WHERE a.col5 <> 0
July 29, 2008 at 1:54 am
"Object a not found..." error occures
July 29, 2008 at 2:17 am
This works on my machine. I removed the a. in the set clause, see if it helps...
CREATE TABLE #temptable1(col1 int , col2 int, col3 int, col4 int, col5 int)
CREATE TABLE #temptable2(col1 int , col2 int, col3 int, col4 int, col5 int)
insert into #temptable1 values(1,1,1,1,1)
insert into #temptable2 values(2,3,1,1,1)
UPDATE a
SET col1 = b.col1,
col2 = b.col2
FROM #temptable1 a
RIGHT OUTER JOIN #temptable2 b ON a.col3=b.col3
AND a.col4=b.col4
WHERE a.col5 <> 0
July 29, 2008 at 3:16 am
Will surely try this.
My other issue is I have to get this working in Sybase as well which probably does not allow this syntax.
Anyway, thank you so much for the help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply