SQL Server Error: Maximum Number of Prefixes. The maximum is 3. with join syntax

Question

Attempt to run cross-server update:

UPDATE ASILIVE.CustomerManagementSystem.dbo.Sessions SET ASILIVE.CustomerManagementSystem.dbo.Sessions.VarianceAmount=Variances.VarianceAmount FROM ASILIVE.CustomerManagementSystem.dbo.Sessions INNER JOIN Variances ON ASILIVE.CustomerManagementSystem.dbo.Sessions.SessionGUID = Variances.SessionGUID WHERE ASILIVE.CustomerManagementSystem.dbo.Sessions.VarianceAmount <> Variances.VarianceAmount 

Gives an error:

 Msg 117, Level 15, State 2, Line 5 The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains more than the maximum number of prefixes. The maximum is 3. 

What gives?

---

see also

• SQL Server Error: Maximum Number of Prefixes. 3 "maximum with subquery syntax
  (Suggestions with sub-syntax, this question relates to join syntax)

---

Unimportant research:

I tried randomly aliasing s :

 UPDATE ASILIVE.CustomerManagementSystem.dbo.Sessions s SET s.VarianceAmount=Variances.VarianceAmount FROM ASILIVE.CustomerManagementSystem.dbo.Sessions s INNER JOIN Variances ON s.SessionGUID = Variances.SessionGUID WHERE s.VarianceAmount <> Variances.VarianceAmount 

But this does not work:

 Msg 117, Level 15, State 2, Line 5 The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains more than the maximum number of prefixes. The maximum is 3. 

Hamlin suggested adding brackets:

 UPDATE [ASILIVE].[CustomerManagementSystem].dbo.Sessions SET [ASILIVE].[CustomerManagementSystem].dbo.Sessions.DisciplineVarianceAmount=DisciplineVariances.VarianceAmount FROM [ASILIVE].[CustomerManagementSystem].dbo.Sessions INNER JOIN DisciplineVariances ON [ASILIVE].[CustomerManagementSystem].dbo.Sessions.SessionGUID = DisciplineVariances.SessionGUID WHERE [ASILIVE].[CustomerManagementSystem].dbo.Sessions.DisciplineVarianceAmount <> DisciplineVariances.VarianceAmount 

but this does not work:

 Msg 117, Level 15, State 2, Line 5 The number name 'ASILIVE.CustomerManagementSystem.dbo.Sessions' contains more than the maximum number of prefixes. The maximum is 3. 

Answer 1

Do you really like typing a lot ?:-)

 UPDATE s SET s.DisciplineVarianceAmount = v.VarianceAmount FROM [ASILIVE].[CustomerManagementSystem].dbo.Sessions AS s INNER JOIN dbo.Variances AS v ON s.SessionGUID = v.SessionGUID AND s.VarianceAmount <> v.VarianceAmount; 

Note that you may need to describe what to do here if the deviation is currently NULL .

Answer 2

Often, you need to add brackets at least around your linked server name.

 [ASILIVE].[CustomerManagementSystem].dbo.Sessions 

EDIT - try optional

 UPDATE S SET DisciplineVarianceAmount = Variances.VarianceAmount FROM [ASILIVE].[CustomerManagementSystem].dbo.Sessions as S INNER JOIN Variances ON S.SessionGUID = Variances.SessionGUID WHERE S.VarianceAmount <> Variances.VarianceAmount