如何在同一个查询中进行两次UPDATE

Question

我试图在同一查询中使用UPDATE两次来修改我的TABLE数据。你能解释一下我是怎么做的吗?我在以下代码上得到一个语法错误:

UPDATE HRBI SET HRBI.PayGroupLocationPayGroupCountryCode = IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'VP', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'SVP', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'FEL', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'DIR', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'SFL', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'STR', 'DEU - Exec',
IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' AND HRBI.ManagementLevel = 'EVP', 'DEU - Exec', 
IIF(HRBI.WorkerID = '45' OR HRBI.WorkerID = '46' OR HRBI.WorkerID = '47' OR HRBI.WorkerID = '48' OR HRBI.WorkerID = '49' OR HRBI.WorkerID = '50' OR HRBI.WorkerID = '51', 'DEU - Exec', HRBI.PayGroupLocationPayGroupCountryCode))))))))
UPDATE SET HRBI.PayGroupCountryDesc = IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec', "Germany - Exec',HRBI.PayGroupCountryDesc);

我试图在另一个UPDATE的末尾添加一个额外的UPDATE。此外，是否有一种方法可以在Access SQL中添加COMMENT ?谢谢!

Answer 1

这是疯狂的SQL。

你的set语句实际上是说，如果一行中的某些字段符合某些条件，则使HRBI的值生效。PayGroupLocationPayGroupCountryCode = 'DEU - Exec'，否则保持原样。

"某些条件"应该作为where子句实现!

就变成了

    UPDATE HRBI 
    SET HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec'
    WHERE 
    ( HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' 
      AND 
      HRBI.ManagementLevel IN ('VP', 'SVP', ...etc ...)
    )
    OR
    (    HRBI.WorkerID = '45' OR HRBI.WorkerID = '46' OR HRBI.WorkerID = '47' 
      OR HRBI.WorkerID = '48' OR HRBI.WorkerID = '49' OR HRBI.WorkerID = '50' 
      OR HRBI.WorkerID = '51'
    );

注意你可以使用HRBI。WorkerID IN(45,46， .....)，但我将把这个留给你来做…

你还需要重写这个:

    UPDATE SET HRBI.PayGroupCountryDesc =         
    IIF(HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec'
        , "Germany -   Exec'
        ,HRBI.PayGroupCountryDesc);

可能像这样嵌入在第一个SQL中

    UPDATE HRBI 
    SET HRBI.PayGroupLocationPayGroupCountryCode = 'DEU - Exec'
       ,HRBI.PayGroupCountryDesc =  "Germany -   Exec'
    WHERE 
    ( HRBI.PayGroupLocationPayGroupCountryCode = 'DEU' 
      AND 
      HRBI.ManagementLevel IN ('VP', 'SVP', ...etc ...)
    )
    OR
    (    HRBI.WorkerID = '45' OR HRBI.WorkerID = '46' OR HRBI.WorkerID = '47' 
      OR HRBI.WorkerID = '48' OR HRBI.WorkerID = '49' OR HRBI.WorkerID = '50' 
      OR HRBI.WorkerID = '51'
    );

好吗?