我想创建和定义命令表的一列（捐赠给我的应用程序），这取决于是否存在连接的一个或另一个条件（如果给我钱的人是 Great Benefactor 或者这些钱来自一个叫做 nuit ). 然后我试着

UPDATE orders
        INNER JOIN contacts ON orders.id = contacts.`Contact ID`
        IF contacts.`Great Benefactor` = true OR orders.Campaign = `nuit`
            SET orders.exclude_from_statistics = 1
        ELSE
            SET orders.exclude_from_statistics = 0
            ;

但它的回报是：

2021-07-07 20:05:53.674026 - creating column excude from statistics
Traceback (most recent call last):
  File "/usr/lib64/python3.9/runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib64/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/ac/Documents/Programming/Work/data-tools/etl/task/exclude.py", line 27, in <module>
    main()
  File "/home/ac/Documents/Programming/Work/data-tools/etl/task/exclude.py", line 23, in main
    add_column_exclude_from_statistics()
  File "/home/ac/Documents/Programming/Work/data-tools/etl/task/exclude.py", line 11, in add_column_exclude_from_statistics
    c.execute("""
  File "/home/ac/Documents/Programming/Work/data-tools/venv/lib64/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/ac/Documents/Programming/Work/data-tools/venv/lib64/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/ac/Documents/Programming/Work/data-tools/venv/lib64/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF contacts.`Great Benefactor` = true OR orders.Campaign = `nuit`\n            SE' at line 3")

我想我的情况不对 IF contacts. 大恩人 = true OR orders.Campaign = 努伊特``这似乎很像Python学。
我还尝试：

IF(contacts.`Great Benefactor` = true OR orders.Campaign = `nuit`
         SET orders.exclude_from_statistics = 1,
         SET orders.exclude_from_statistics = 0)

以下是完整的脚本：

from etl.utils.logging import info
from etl.mysql.connect import db, db_name
from etl.mysql.operations import add_column_if_not_exists

def add_column_exclude_from_statistics():
    add_column_if_not_exists(db, db_name, 'orders', 'exclude_from_statist
    info("creating column excude from statistics")
    c = db.cursor()
    c.execute("""
        UPDATE orders
        INNER JOIN contacts ON orders.id = contacts.`Contact ID`
        IF contacts.`Great Benefactor` = true OR orders.Campaign = `nuit`
            SET orders.exclude_from_statistics = 1
        ELSE
            SET orders.exclude_from_statistics = 0
            ;
        """)

def main():
    info("Column users.segmentation_2019")
    add_column_exclude_from_statistics()

if __name__ == '__main__':
    main()

我试过戈登的答案：

UPDATE orders AS o JOIN
       contacts AS c
       ON o.id = c.`Contact ID`
    SET o.exclude_from_statistics = (c.`Great Benefactor` = true
        OR o.Campaign = 'nuit');""")

但是得到：

2021-07-08 14:41:09.687383 - creating column excude from statistics
Traceback (most recent call last):
  File "/usr/lib64/python3.9/runpy.py", line 197, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/usr/lib64/python3.9/runpy.py", line 87, in _run_code
    exec(code, run_globals)
  File "/home/ac/Documents/Programming/Work/data-tools/etl/task/exclude.py", line 22, in <module>
    main()
  File "/home/ac/Documents/Programming/Work/data-tools/etl/task/exclude.py", line 18, in main
    add_column_exclude_from_statistics()
  File "/home/ac/Documents/Programming/Work/data-tools/etl/task/exclude.py", line 10, in add_column_exclude_from_statistics
    c.execute("""
  File "/home/ac/Documents/Programming/Work/data-tools/venv/lib64/python3.9/site-packages/MySQLdb/cursors.py", line 206, in execute
    res = self._query(query)
  File "/home/ac/Documents/Programming/Work/data-tools/venv/lib64/python3.9/site-packages/MySQLdb/cursors.py", line 319, in _query
    db.query(q)
  File "/home/ac/Documents/Programming/Work/data-tools/venv/lib64/python3.9/site-packages/MySQLdb/connections.py", line 259, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1054, "Unknown column 'o.id' in 'on clause'")