SQL Anywhere 错误 -824: 对相关名表名的引用非法

Question

当我在Sybase IQ上运行此脚本时：

declare @YEAR  int=2017
declare @MON   int=6
declare @DAY  int=7
update MainTable
set MainTable.Amount=(X.Number+Y.Number),
MainTable.Total=(X.Total+Y.Total)
from (select 'Number'= count(*), 'Total'=case when SUM(T1_Total) is null then 0 else SUM(T1_Total) end
from Table1    
where  T1_Account_NO=MainTable.Account_NO 
and    T1_SENTRY_YEAR=@YEAR and T1_SENTRY_MON=@MON and T1_SENTRY_DAY=@DAY) X,
(select 'Number'= count(*), 'Total'=case when SUM(T2_TOTAL) is null then 0 else SUM(T2_TOTAL) end 
from Table2  where T2_Account_NO = MainTable.Account_NO 
and  T2_YEAR=@YEAR and T2_MON=@MON and T2_DAY=@DAY )Y 
where       MainTable.YEAR=@YEAR
and   MainTable.MON = @MON
and   MainTable.DAY=@DAY

我收到这样的错误："SQL Anywhere 错误 -824：对相关名 MainTable 的非法引用">

我怎样才能克服这个问题？

Answer 1

您是否尝试过将 MainTable 添加到from子句中，例如：

update Maintable
set    ...
from   MainTable,
(select ... )X,
(select ... )Y
where  ...

---

注意：我使用 Sybase ASE，它不允许在派生表中引用"外部"相关名，所以我想知道 SQLAnywhere 是否有类似的限制...... ？

如果将 MainTable 联接拉出到查询的最顶层会发生什么情况，例如：

declare @YEAR  int=2017
declare @MON   int=6
declare @DAY  int=7
update MainTable
set MainTable.Amount=(X.Number+Y.Number),
MainTable.Total=(X.Total+Y.Total)
from (select T1_account_NO, 'Number'= count(*), 'Total'=case when SUM(T1_Total) is null then 0 else SUM(T1_Total) end
from Table1    
where  T1_SENTRY_YEAR=@YEAR and T1_SENTRY_MON=@MON and T1_SENTRY_DAY=@DAY
group by T1_Account_NO) X,
(select T2_Account_NO, 'Number'= count(*), 'Total'=case when SUM(T2_TOTAL) is null then 0 else SUM(T2_TOTAL) end 
from Table2  where T2_YEAR=@YEAR and T2_MON=@MON and T2_DAY=@DAY 
group by T2_Account_NO)Y 
where       MainTable.YEAR=@YEAR
and   MainTable.MON = @MON
and   MainTable.DAY=@DAY
and   MainTable.Account_NO = X.T1_Account_NO
and   MainTable.Account_NO = Y.T2_Account_NO

一个潜在的与性能相关的缺点是，如果派生表现在生成大量不会与 MainTable 联接的记录(除非 SQLAnywhere 查询引擎能够以某种方式平展查询...... ???(。

---

如果这是不允许派生表中的"外部"相关名的问题，另一个(显而易见的？(解决方案是根据将 MainTable 与 Table1/Table2 连接的结果创建几个 #temp 表，然后执行 MainTable 的更新作为与 #temp 表的连接。[如果数据量足够大，可以在性能方面证明索引的合理性，则可能会为 #temp 表编制索引。

Answer 2

您是否尝试将 MainTable 添加到 FROM 子句中？

Answer 3

我像这样克服了这个问题：

declare @YEAR  int=2017
declare @MON   int=6
declare @DAY   int=7
update MainTable
set MainTable.Amount= (X.Number),
MainTable.Total = (X.Total)
from (select T1_Account_NO,'Number'= count(*), 'Total'=case when SUM(T1_Total) is null then 0 else SUM(T1_Total) end
from Table1    
where T1_SENTRY_YEAR=@YEAR and T1_SENTRY_MON=@MON and T1_SENTRY_DAY=@DAY
group by T1_Account_NO) X,
where  X.T1_Account_NO=MainTable.Account_NO
and   MainTable.YEAR=@YEAR
and   MainTable.MON = @MON
and   MainTable.DAY=@DAY

update MainTable
set MainTable.Amount= coalesce(MainTable.Amount,0)+(Y.Number),
MainTable.Total = coalesce(MainTable.Total,0)+(Y.Total)
(select T2_Account_NO,'Number'= count(*), 'Total'=case when SUM(T2_TOTAL) is null then 0 else SUM(T2_TOTAL) end 
from Table2  
where T2_YEAR=@YEAR and T2_MON=@MON and T2_DAY=@DAY 
group by T2_Account_NO) Y 
where       MainTable.YEAR=@YEAR
and   MainTable.MON = @MON
and   MainTable.DAY=@DAY
and   Y.T2_Account_NO = MainTable.Account_NO

我已经将更新脚本分开了这两个部分。