jeudi 8 juillet 2021

How to do an IF (X OR Y) in MySQL 5.6

I want to create and define a column of a command table (donations to my application) depending on whether there is one or another condition of a join (if the person giving me money is a Great Benefactor or the money is from a campaign called nuit). Then I tried

        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
            ;

But it returns:

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")

Pienso que me equivoco en la condición IF contacts.Great Benefactor= true OR orders.Campaign =nuit`` Parece que es muy pythonista

Here is the full script:

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()

Aucun commentaire:

Enregistrer un commentaire