mysql - 更新内部左连接上的列

Question

我无法弄清楚如何对此查询进行内部左连接更新(这是有效的选择查询):

SELECT
    *
FROM
    emr AS e
    LEFT JOIN visit_ltnot AS v ON e.guid = v._pn
WHERE
    closedate='12-31-79' AND pat='00000906' AND apptstatus=''
    AND v.pdate='' AND emrdate <= '2013-11-26';

我需要更新 emr 表上的关闭日期。

我已经绑定(bind)了以下查询:

UPDATE emr
FROM
    emr AS e
    LEFT JOIN visit_ltnot AS v ON e.guid = v._pn SET closedate=''
WHERE
    closedate='12-31-79' AND pat='00000906' AND apptstatus=''
    AND v.pdate='' AND emrdate <= '2013-11-26';

UPDATE emr
    SET closedate=''
FROM
    emr AS e
    LEFT JOIN visit_ltnot AS v ON e.guid = v._pn
WHERE
    closedate='12-31-79' AND pat='00000906' AND apptstatus=''
    AND v.pdate='' AND emrdate <= '2013-11-26';

我收到语法错误，我需要更新关闭值。

Answer 1

如果您使用 MySQL，那么这应该可以工作:

UPDATE emr e LEFT JOIN
       visit_ltnot v
       ON e.guid = v._pn
    SET closedate = ''
    WHERE closedate = '12-31-79' AND
          pat = '00000906' AND
          apptstatus = '' AND
          v.pdate = '' AND
          emrdate <= '2013-11-26';

注释:

• 最重要的是，UPDATE 在 MySQL 中没有 FROM 子句。
• 条件v.pdate正在将外连接转换为内连接。因此，您不妨使用内连接。
• 您不应使用诸如'12-31-79'之类的日期格式。正确的日期格式为'1979-12-31'。
• 日期不应存储为字符串，因此 v.pdate = '' 没有意义。