How to write update statement with multiple table Join in Parallel Data Warehouse?

Question

I am having difficulty trying to write an update statement On TableA with Joining of TableA,TableB,TableC in Parallel Data Warehouse, but it is throwing an exception.

E.g : Simple Example

Update TableA
Set EnableFlag='Y'

From TableA INNER JOIN TableB ON A.ID=B.ID  
INNER JOIN TableC ON A.ID=C.ID

What could be causing this?

Answer 1

UPDATE on PDW is a bit strange and has some limits as you have found. Basically you need to do a CTAS first. Then you can either join to the CTAS table during the UPDATE or just rename the old table and replace with the new CTAS table via rename. Here's an example of CTAS then join during UPDATE:

CREATE TABLE CTAS_ACS
WITH (DISTRIBUTION = REPLICATE)
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 s
JOIN  dbo.DimDate d               ON s.OrderDateKey            = d.DateKey
JOIN  dbo.DimProduct p            ON s.ProductKey              = p.ProductKey
JOIN  dbo.DimProductSubCategory u ON p.[ProductSubcategoryKey] = u.[ProductSubcategoryKey]
JOIN  dbo.DimProductCategory c    ON u.[ProductCategoryKey]    = c.[ProductCategoryKey]
WHERE [CalendarYear] = 2004
GROUP BY [EnglishProductCategoryName]
,        [CalendarYear];

UPDATE AnnualCategorySales
SET   AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM  CTAS_ACS
WHERE CTAS_ACS.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND   CTAS_ACS.[CalendarYear]               = AnnualCategorySales.[CalendarYear];

While the above pattern is the typical one, in your case I think the following would work (off the top of my head):

Update TableA
Set EnableFlag='Y'
WHERE ID IN (
 SELECT B.ID
 FROM TableB B
 INNER JOIN TableC C ON B.ID=C.ID
)