SQL - (Update, From) statement with inner join on the same table

Question

I have the below SQL query, the purpose of this query to detect a missing sequence: for example if I have seq 1,2,3,5... it should update the record 5 with a message "Previous sequence is missing".

Am trying to do this logic using update from inner join statement as follows, although its giving error on line 1 that TblA is ambiguous:

update      dbo.TblA 
set         Msg = 'Previous sequence is missing'
from        dbo.TblA R1
left join   dbo.TblA R2 
on          (R2.Sequence = R1.Sequence -1) 
and         (R2.StatementNumber = R1.StatementNumber)
where       R2.TransID is  null and R1.Sequence <> 1

I know that this can be easy fixed by nested queries but am thinking of something more organized and neat :)

Answer 1

Use this query. It doesn't set an alias on the table to update, just on the left join.

update      dbo.TblA 
set         Msg = 'Previous sequence is missing'
from        dbo.TblA 
left join   dbo.TblA R
on          (R.Sequence = TblA.Sequence -1) 
and         (R.StatementNumber = TlbA.StatementNumber)
where       R.TransID is  null and Tbla.Sequence <> 1