SQL Server错误:前缀的最大数量.最多3个. [英] SQL Server Error: maximum number of prefixes. The maximum is 3. with join syntax

问题描述

尝试运行跨服务器更新:

Trying to run a 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

给出错误:

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.

有什么作用?

• SQL Server错误:带有子选择语法的最大前缀数.最大为3"
  (使用子选择语法进行交易；此问题涉及连接语法)

• SQL Server Error: "maximum number of prefixes. The maximum is 3" with subselect syntax
  (Deals with sub-select syntax; this question deals with join syntax)

不重要的研究:

我尝试将事物随机别名为s:

i tried randomly aliasing things to 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

但这不起作用:

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建议添加括号:

Hamlin suggested added 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

但这不起作用:

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.

推荐答案

您真的喜欢很多打字吗? :-)

Do you really like a lot of typing? :-)

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;

请注意，如果当前任一方差金额为NULL，您可能想在此处说明要执行的操作.

Take note that you may want to describe what to do here if either variance amount is currently NULL.

这篇关于SQL Server错误:前缀的最大数量.最多3个.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！