CODEWITHSUNDEEP
sqlsql-servert-sql
Trunks
Trunks

Reputation: 151

Update not working using alias and nested select

I'm trying to update number of records in my table but using combination of alias and nested select I was not able to update and I get this error

Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'a'

This is my code:

update BranchTagging a set
    Date_End = '2021-12-31 00:00:00'
where Branch_ID in (
    select Branch_ID 
    from ##ForTagging2021
    where code = a.Code 
    and Source_Tagging = a.Source_Tagging
)

Upvotes: 0

Views: 1299

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

SQL Server does not allow aliases in the update. So, you could just use the full table name:

update BranchTagging 
    set Date_End = '2021-12-31'
where Branch_ID in (select ft.Branch_ID
                    from ##ForTagging2021 ft
                    where ft.code = BranchTagging.Code and
                          ft.Source_Tagging = BranchTagging.Source_Tagging
                   );

It seems a little odd to me to use in with a correlated subquery. More typically, I would just use exists.

Also, you can assign aliases if you use a from clause, which would be an alternative way to formulate the query. The above, though, is standard SQL.

Upvotes: 1

Related Questions