Hi,

My DB is MS SQL Server. I wrote a SQL statement to remove rows from a table as following.

DELETE FROM #TableA
FROM #TableA AS t1
LEFT JOIN #TableA AS t2 ON (t1.Index - 1) = t2.Index
WHERE t2.Index IS NULL

========================================

When I tried to execute it, I got the following error message.
Error Message: The table '#TableA' is ambiguous.

Thanks for your help.

Interesting problem, never thought about it. I can imagine that MSSQL has some problem with guessing what to do.

I know it is not an answer to your question, the following syntax should do the same (if I got the query right) :

DELETE FROM #TableA
WHERE Index-1 NOT IN (SELECT Index FROM #TableA)

ps. Do you really have columns named "Index"

You have answered my question.
Many Thanks.

I don't have a column named "Index", it is a alias to simply my question.