• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

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

sql-server-2008 来源:Teejay 3次浏览

我试图改写为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") 
; 

这将SELECTION派生表加入"LOAD"."UNITS_OF_MEASURE",并使用派生表的数据更新其他表的列。


版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)