多个更新凡在SQL Server中存在2008

Z时代
2024-01-10
分类：问答

我试图改写为SQL Server 2008这Oracle查询：多个更新凡在SQL Server中存在2008

UPDATE "LOAD"."UNITS_OF_MEASURE" 
SET ("UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE", "UOM_REFERENCE", "UOM_SUBTYPE_ID") = 
       (SELECT "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE", 0 AS "UOM_REFERENCE", NULL AS "UOM_SUBTYPE_ID" 
        FROM (SELECT DISTINCT "UOM_TYPE_ID", "UOM_CODE", "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE" 
          FROM "LOAD"."LOADER_UNITS_OF_MEASURE" 
          INNER JOIN "LOAD"."UOM_TYPES" ON ("UOM_TYPES"."UOM_TYPE_CODE"="LOADER_UNITS_OF_MEASURE"."UOM_TYPE_CODE") 
         ) SELECTION 
        WHERE ("SELECTION"."UOM_TYPE_ID"="UNITS_OF_MEASURE"."UOM_TYPE_ID") 
        AND ("SELECTION"."UOM_CODE"="UNITS_OF_MEASURE"."UOM_CODE") 
       ) 
WHERE EXISTS (SELECT "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE", 0 AS "UOM_REFERENCE", NULL AS "UOM_SUBTYPE_ID" 
        FROM (SELECT DISTINCT "UOM_TYPE_ID", "UOM_CODE", "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE" 
          FROM "LOAD"."LOADER_UNITS_OF_MEASURE" 
          INNER JOIN "LOAD"."UOM_TYPES" ON ("UOM_TYPES"."UOM_TYPE_CODE"="LOADER_UNITS_OF_MEASURE"."UOM_TYPE_CODE") 
         ) SELECTION 
       WHERE ("SELECTION"."UOM_TYPE_ID"="UNITS_OF_MEASURE"."UOM_TYPE_ID") 
       AND ("SELECTION"."UOM_CODE"="UNITS_OF_MEASURE"."UOM_CODE") 
)

内SELECT查询应保持不变，因为查询会自动组成一个独立于DBMS的查询构建器层。

我试过了，除其他外，语法如下：

UPDATE "LOAD"."UNITS_OF_MEASURE" 
SET "UOM_DESC" = i."UOM_DESC", "UOM_SYMBOL" = i."UOM_SYMBOL", "UOM_MUL_FACTOR" = i."UOM_MUL_FACTOR", "UOM_ADD_FACTOR" = i."UOM_ADD_FACTOR", "UOM_DEC_PLACE" = i."UOM_DEC_PLACE", 
    "UOM_REFERENCE" = 0, "UOM_SUBTYPE_ID" = NULL FROM 
       (SELECT "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE", 0 AS "UOM_REFERENCE", NULL AS "UOM_SUBTYPE_ID" 
        FROM (SELECT DISTINCT "UOM_TYPE_ID", "UOM_CODE", "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE" 
          FROM "LOAD"."LOADER_UNITS_OF_MEASURE" 
          INNER JOIN "LOAD"."UOM_TYPES" ON ("UOM_TYPES"."UOM_TYPE_CODE"="LOADER_UNITS_OF_MEASURE"."UOM_TYPE_CODE") 
         ) SELECTION 
        WHERE ("SELECTION"."UOM_TYPE_ID"="UNITS_OF_MEASURE"."UOM_TYPE_ID") 
        AND ("SELECTION"."UOM_CODE"="UNITS_OF_MEASURE"."UOM_CODE") 
       )i 
WHERE EXISTS (SELECT "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE", 0 AS "UOM_REFERENCE", NULL AS "UOM_SUBTYPE_ID" 
        FROM (SELECT DISTINCT "UOM_TYPE_ID", "UOM_CODE", "UOM_DESC", "UOM_SYMBOL", "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE" 
          FROM "LOAD"."LOADER_UNITS_OF_MEASURE" 
          INNER JOIN "LOAD"."UOM_TYPES" ON ("UOM_TYPES"."UOM_TYPE_CODE"="LOADER_UNITS_OF_MEASURE"."UOM_TYPE_CODE") 
         ) SELECTION 
       WHERE ("SELECTION"."UOM_TYPE_ID"="UNITS_OF_MEASURE"."UOM_TYPE_ID") 
       AND ("SELECTION"."UOM_CODE"="UNITS_OF_MEASURE"."UOM_CODE") 
)

上线9-10

可惜"UNITS_OF_MEASURE"."UOM_TYPE_ID"和"UNITS_OF_MEASURE"."UOM_CODE"不能得到解决。

回答：

基本上，这看起来好像您将派生表与外部表（UPDATE的目标）关联起来，而不是将它与其连接到后者。这在Transact-SQL中不起作用（我也怀疑这是否会在Oracle中起作用，事实上）。

但是，您可以使用UPDATE...FROM语法：刚刚摆脱中间层的选择和使用最内层的SELECT作为派生表加入到UNITS_OF_MEASURE，即是这样的：

UPDATE "LOAD"."UNITS_OF_MEASURE" 
SET "UOM_DESC" = "SELECTION"."UOM_DESC", 
    "UOM_SYMBOL" = "SELECTION"."UOM_SYMBOL", 
    "UOM_MUL_FACTOR" = "SELECTION"."UOM_MUL_FACTOR", 
    "UOM_ADD_FACTOR" = "SELECTION"."UOM_ADD_FACTOR", 
    "UOM_DEC_PLACE" = "SELECTION"."UOM_DEC_PLACE", 
    "UOM_REFERENCE" = 0, 
    "UOM_SUBTYPE_ID" = NULL 
FROM (SELECT DISTINCT "UOM_TYPE_ID", "UOM_CODE", "UOM_DESC", "UOM_SYMBOL", 
         "UOM_MUL_FACTOR", "UOM_ADD_FACTOR", "UOM_DEC_PLACE" 
     FROM "LOAD"."LOADER_UNITS_OF_MEASURE" 
     INNER JOIN "LOAD"."UOM_TYPES" ON ("UOM_TYPES"."UOM_TYPE_CODE"="LOADER_UNITS_OF_MEASURE"."UOM_TYPE_CODE") 
    ) SELECTION 
WHERE ("SELECTION"."UOM_TYPE_ID"="UNITS_OF_MEASURE"."UOM_TYPE_ID") 
    AND ("SELECTION"."UOM_CODE"="UNITS_OF_MEASURE"."UOM_CODE") 
;