sql - 从另一个表中提取的条件更新 - Access 语法错误

Question

我是 MS Access 的初学者。

我有两个表 - workrelationship 给出了一个人每次为组织工作的日期(我们显然终止并重新雇用了很多)，workterms 给出了开始/停止系统中每次员工更改的日期 - 新地址、姓名更改等。因此，一个workrelationship 期间可以有多个workterms 期间。

在 workrelationship 中有一列 POS(服务期间 id)，我需要将其复制到 workterms 中，以适应落入的每个 wt 日期范围在wr 日期范围内。然后，我需要删除 workterms 中日期不在 wr 范围内的每一行。

workterms 中有大约 50k 行，我需要正确处理，所以我在 Access 中设置了示例表以供使用。 Here's a link到两个表和错误消息(见下文)。

我的计划是先拉入 POS 并将不受工作关系日期限制的任何内容标记为“删除”。然后我可以对 POS 列中显示“删除”的每一行进行删除。 (DELETE FROM workterms WHERE pos = 'delete';)

我得到一个 syntax error in Access当我做第一部分时:

UPDATE workterms
SET wt.pos =
    CASE WHEN (wt.termstart >= wr.originalstart 
    and wt.termend <= wr.finalend) 
    THEN wr.pos
    ELSE 'delete' 
    END
FROM workterms wt
INNER JOIN workrelationship wr
ON wr.personid = wt.personid

关于语法错误的任何想法？它突出显示了第 3 行的“WHEN”一词。我不熟悉 Access，但我一直在搜索，与我找到的相比，这看起来是正确的。

编辑:我也试过 IIF:

UPDATE workterms
SET wt.pos =
    IIF ((wt.termstart >= wr.originalstart 
    and wt.termend <= wr.finalend), wr.pos, 'delete')
FROM workterms wt
INNER JOIN workrelationship wr
ON wr.personid = wt.personid;

编辑 2:HansUp 下面的查询运行得很好(谢谢!!)，它解决了语法问题。

UPDATE workterms wt
    INNER JOIN workrelationship wr
    ON wr.personid = wt.personid
SET wt.pos =
    IIF(
        wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend,
        wr.pos,
        'delete'
        );

编辑 3:但是 - it only brought 3 values in ，我期待 8，正如您通过下面的选择和链接电子表格中的“从编辑 3 中选择”选项卡所看到的:

SELECT wr.*, wt.*
FROM workrelationship wr, workterms wt
WHERE wr.personid = wt.personid 
and wt.termstart >= wr.originalstart 
and wt.termend <= wr.finalend

Answer 1

您当前的 UPDATE 查询未提供您想要的结果，因为 INNER JOIN 仅在 [personid] 上进行。因此，对于在 [workrelationship] 中多次出现的任何 [personid]，您的 UPDATE 查询会生成多个相互冲突的结果。

考虑以下模拟 [personid]=2 的 UPDATE 查询行为的 SELECT 查询:

SELECT 
    IIF(
        wt.termstart >= wr.originalstart AND wt.termend <= wr.finalend,
        wr.pos,
        'delete'
        ) AS result,
    wt.personid,
    wt.termstart,
    wt.termend,
    wt.pos AS wt_pos,
    wr.originalstart,
    wr.finalend,
    wr.pos AS wr_pos
FROM 
    workterms wt
    INNER JOIN 
    workrelationship wr
        ON wr.personid = wt.personid 
WHERE wt.personid = 2
ORDER BY wt.termstart, wr.originalstart

结果是

result  personid  termstart   termend     wt_pos  originalstart  finalend    wr_pos
------  --------  ----------  ----------  ------  -------------  ----------  ------
200-1          2  2010-02-01  2010-03-01  blah    2010-02-01     2010-05-01  200-1 
delete         2  2010-02-01  2010-03-01  blah    2010-07-01     2010-10-01  200-2 
200-1          2  2010-03-01  2010-05-01  blah    2010-02-01     2010-05-01  200-1 
delete         2  2010-03-01  2010-05-01  blah    2010-07-01     2010-10-01  200-2 
delete         2  2010-07-01  2010-10-01  blah    2010-02-01     2010-05-01  200-1 
200-2          2  2010-07-01  2010-10-01  blah    2010-07-01     2010-10-01  200-2 

请注意，每个 [workterms] 行出现两次，[workrelationship] 中的每个相应行出现一次，并且 [result] 因连接的 [workrelationship] 行而异。因此，您的 UPDATE 查询实际上会更新每个 [workterms] 行两次。 wt.pos 的最终值将是最后应用的值，应用更新的顺序完全取决于查询优化器。

因此，您需要做的是分两次应用更新。首先将所有 wt.pos 值设置为“删除”

UPDATE workterms SET pos = 'delete'

然后使用连接 [personid] 值 和 日期的查询更新您要保留的八 (8) 行的 wt.pos 值:

UPDATE 
    workterms wt
    INNER JOIN 
    workrelationship wr
        ON wr.personid = wt.personid 
            AND wt.termstart >= wr.originalstart 
            AND wt.termend  <= wr.finalend
SET wt.pos = wr.pos

现在您的 [workterms] 表将如下所示:

personid  termstart   termend     pos   
--------  ----------  ----------  ------
       1  2010-08-01  2010-09-01  100-1 
       1  2010-03-01  2010-05-01  100-1 
       2  2010-07-01  2010-10-01  200-2 
       2  2010-03-01  2010-05-01  200-1 
       2  2010-02-01  2010-03-01  200-1 
       3  2010-11-01  2015-12-01  delete
       3  2010-09-01  2010-10-01  300-2 
       3  2010-08-01  2010-09-01  300-2 
       3  2010-07-05  2010-07-15  delete
       3  2010-05-01  2010-07-01  300-1