从 Azure SQL DW 更新? [英] UPDATE FROM in Azure SQL DW?

问题描述

我在尝试执行 UPDATE FROM 查询的 Azure SQL DW 中收到错误消息.错误是UPDATE 和 DELETE 语句中的 FROM 子句不能包含子查询源或连接"

I'm receiving an error in Azure SQL DW trying to do an UPDATE FROM query. The error is "FROM clause in UPDATE and DELETE statements cannot contain subquery sources or joins"

这只是特定于 SQL DW 吗?否则，我认为此查询没有任何问题.如果是 SQL DW 的限制，还有什么办法?

Is this just specific to SQL DW? I don't see anything wrong with this query otherwise. If it is a limitation of SQL DW, what's the alternative?

-- Permanent fact table with 5 billion rows
CREATE TABLE FactTable (Id1 INT, Id2 INT, EmailAddress NVARCHAR(100), Value1 INT)
WITH (DISTRIBUTION = HASH(EmailAddress));

-- Staging fact table with 10 million rows    
CREATE TABLE StageTable (Id1 INT, Id2 INT, EmailAddress NVARCHAR(100), Value1 INT)
WITH (DISTRIBUTION = HASH(EmailAddress), HEAP);

-- Add a secondary index that should help with joining to StageTable
CREATE NONCLUSTERED INDEX ix ON FactTable (Id1, Id2);

UPDATE fact
SET
   Value1 = CASE WHEN stage.Value1 > fact.Value1 THEN stage.Value1 ELSE fact.Value1 END
FROM FactTable AS fact
INNER JOIN StageTable AS stage ON fact.Id1 = stage.Id1 AND fact.Id2 = stage.Id2

推荐答案

根据 文档 Azure SQL 数据仓库支持 UPDATE 但不支持 FROM 子句中的 ANSI 联接.您可以使用 CTAS 来解决.简单的两表更新:

As per the documentation Azure SQL Data Warehouse does support UPDATE but does not support ANSI joins in the FROM clause. You can use CTAS to work around. Simple two-table update:

UPDATE dbo.FactTable
SET
   Value1 = CASE WHEN stage.Value1 > dbo.FactTable.Value1 THEN stage.Value1 ELSE dbo.FactTable.Value1 END
FROM dbo.StageTable AS stage
WHERE dbo.FactTable.Id1 = stage.Id1 
  AND dbo.FactTable.Id2 = stage.Id2;

更复杂的 CTAS 示例，从 主更新文档页面:

More complex example with CTAS, copied wholesale from the main UPDATE documentation page:

-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT  ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0)    AS [EnglishProductCategoryName]
,       ISNULL(CAST([CalendarYear] AS SMALLINT),0)                      AS [CalendarYear]
,       ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0)                     AS [TotalSalesAmount]
FROM    [dbo].[FactInternetSales]       AS s
JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
WHERE   [CalendarYear] = 2004
GROUP BY
        [EnglishProductCategoryName]
,       [CalendarYear]
;

-- Use an implicit join to perform the update
UPDATE  AnnualCategorySales
SET     AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM    CTAS_acs
WHERE   CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND     CTAS_acs.[CalendarYear]               = AnnualCategorySales.[CalendarYear]
;

--Drop the interim table
DROP TABLE CTAS_acs
;

这篇关于从 Azure SQL DW 更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！