sql-server - SQL Server 你可以更新通过多个 UNION ALL 创建的表中的元素吗

Question

我创建了一个由多个不同表组成的表。我正在尝试更新大型企业集团表中的特定元素，但无法 - 不断收到语法错误。有没有办法更新由多个表组成的表中的元素？

WITH sel AS
(
  SELECT MIN(attribute_value) minyear,
         MAX(attribute_value) maxyear
  FROM ais.dms_attributes
  WHERE attribute_name = 'Year'
  AND   ISNUMERIC(attribute_value) = 1
),
rec AS
(
  SELECT CAST(minyear AS INT) minyear
  FROM sel
  UNION ALL
  SELECT (CAST(minyear AS INT) +1) minyear
  FROM rec
  WHERE minyear <(SELECT maxyear FROM sel)
),
allrows AS
(
  SELECT DISTINCT -1 ENTITY_ID,
         attribute_value ENTITY_NAME,
         CASE
           WHEN attribute_name = 'tenant' THEN 'TENANT'
           WHEN attribute_name = 'investment' THEN 'INVESTMENT'
           WHEN attribute_name = 'asset' THEN 'ASSET'
           ELSE attribute_name
         END AS ENTITY_TYPE
  FROM a.attributes
  WHERE attribute_name IN ('tenant','investment','asset')
  AND   attribute_value IS NOT NULL
  AND   charindex('[',attribute_value) < 1
  UNION ALL 
  SELECT CTYPE ENTITY_ID,
         TYPE ENTITY_NAME,
         'TYPE' ENTITY_TYPE
  FROM a.types 
  UNION ALL
  SELECT CGROUP ENTITY_ID,
         GROUP ENTITY_NAME,
         'GROUP' ENTITY_TYPE
  FROM a.group
  UPDATE a.group SET GROUP = 'Legacy' WHERE GROUP = 'Regular'
  UNION ALL
  SELECT -1,
         CAST(minyear AS VARCHAR) ENTITY_NAME,
         'YEAR' ENTITY_TYPE
  FROM rec
)
SELECT *
FROM allrows 
ORDER BY CASE
           WHEN ENTITY_TYPE = 'YEAR' THEN ENTITY_NAME
         END DESC,
         CASE
           WHEN ENTITY_TYPE <> 'YEAR' THEN ENTITY_NAME
         END 

标签： sql-servertsql