代码之家  ›  专栏  ›  技术社区  ›  Mutation Person

如何转换此PL/SQL update语句以使其在SQL SERVER中工作?

  •  0
  • Mutation Person  · 技术社区  · 15 年前

    我被要求将此语句从PL/SQL转换为SQL Server:

    UPDATE pdi_nb_process_complete pdi_end1
    SET    (pdi_end1.adp_issue_date_time, pdi_end1.adp_print_date_time) = 
    (SELECT DISTINCT pdi_end1.completion_date + 2
                    ,pdi_end1.completion_date
     FROM   cl100 cl
     WHERE  cl.polref = pdi_end1.policy_reference
     AND    cl.doctyp = 'PSP')
    WHERE  pdi_end1.adp_print_date_time IS NULL
    

    UPDATE pdi_end1
    SET    pdi_end1.adp_issue_date_time = pdi_end1.completion_date + 2,
           pdi_end1.adp_print_date_time = pdi_end1.completion_date
    from pdi_nb_process pdi_end1
    INNER JOIN cl100 cl ON  cl.polref = pdi_end1.policy_reference
         AND cl.doctyp = 'PSP'
    WHERE  pdi_end1.adp_print_date_time IS NULL
    

    但这让我缺少了 不同的 . 有人有什么建议吗?

    2 回复  |  直到 15 年前
        1
  •  2
  •   KM.    15 年前

    和cl.doctyp='PSP'

    试试这个:

    UPDATE pdi_end1
        SET pdi_end1.adp_issue_date_time=pdi_end1.completion_date + 2
            ,pdi_end1.adp_print_date_time)=pdi_end1.completion_date
        FROM pdi_nb_process_complete pdi_endl
            INNER JOIN cl100               cl ON cl.polref=pdi_end1.policy_reference
        WHERE pdi_end1.adp_print_date_time IS NULL
            AND cl.doctyp='PSP' 
    

    SET NOCOUNT ON
    DECLARE @Test table (RowID int, RowValue int, OtherRowValue int)
    INSERT INTO @Test VALUES ( 1, 1, 10)
    INSERT INTO @Test VALUES ( 2, 2, 20)
    INSERT INTO @Test VALUES ( 3, 3, 30)
    INSERT INTO @Test VALUES ( 4, 4, 40)
    INSERT INTO @Test VALUES ( 5, 5, 50)
    INSERT INTO @Test VALUES ( 6, 6, 60)
    INSERT INTO @Test VALUES ( 7, 7, 70)
    INSERT INTO @Test VALUES ( 8, 8, 80)
    INSERT INTO @Test VALUES ( 9, 9, 90)
    INSERT INTO @Test VALUES (10,10,100)
    
    DECLARE @TestJoin table (RowID int, RowValue int)
    INSERT INTO @TestJoin VALUES (1,10)
    INSERT INTO @TestJoin VALUES (1,10)
    INSERT INTO @TestJoin VALUES (1,10)
    INSERT INTO @TestJoin VALUES (2,20)
    INSERT INTO @TestJoin VALUES (2,20)
    INSERT INTO @TestJoin VALUES (2,20)
    INSERT INTO @TestJoin VALUES (2,20)
    INSERT INTO @TestJoin VALUES (2,20)
    INSERT INTO @TestJoin VALUES (3,30)
    INSERT INTO @TestJoin VALUES (3,30)
    SET NOCOUNT OFF
    
    SELECT ---------------------------returns 5 rows, DISTINCT would only return 1
        t.OtherRowValue+2
        FROM @Test                t
            INNER JOIN @TestJoin  j ON t.RowID=j.RowID
        WHERE t.OtherRowValue=20
            AND j.RowValue=20
    
    UPDATE t--------------------------updates only 1 row
        SET t.RowValue=t.OtherRowValue+2
        FROM @Test                t
            INNER JOIN @TestJoin  j ON t.RowID=j.RowID
        WHERE t.OtherRowValue=20
            AND j.RowValue=20
    

    输出:

    -----------
    22
    22
    22
    22
    22
    
    (5 row(s) affected)
    
    (1 row(s) affected)
    

        2
  •  0
  •   Andomar    15 年前

    您可以将内部联接更改为子查询:

    inner join (
        select distinct policy_reference
        from cl100
        where doctyp = 'PSP'
    ) cl on cl.polref = pdi_end1.policy_reference
    

    WHERE  pdi_end1.adp_print_date_time IS NULL
    AND EXISTS (
        select * 
        from cl100 cl
        where cl.polref = pdi_end1.policy_reference
        and cl.doctyp = 'PSP'
    )