1

I'm trying to update some records using this query which is basically just a way to set a "state" to a customer considering their "days since last order":

        UPDATE customers AS c1
        INNER JOIN (
          SELECT id, DATEDIFF(NOW(), agg.cdt) AS acdt
          FROM customers
          INNER JOIN
            (
              SELECT c.id AS cid, max(o.datetime) as cdt
              FROM customers AS c
              LEFT JOIN orders o ON o.customer_id = c.id
              WHERE o.state = 20
              GROUP BY c.id
            ) AS agg ON customers.id = agg.cid
          WHERE account_type IN (1, 2)
            AND deleted = 0
            AND (account_management_state IN (0, 1, 2) OR account_management_state IS NULL)
        ) AS c2 ON c1.id = c2.id
        SET c1.account_management_state = CASE
          WHEN c2.acdt <= 90 THEN 0
          WHEN c2.acdt >= 91 AND c2.acdt <= 360 THEN 1
          WHEN c2.acdt > 360 OR c2.acdt IS NULL THEN 2
          END
        WHERE c1.id = c2.id;

But I'm getting:

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

I'm using the table key in the final WHERE, c1.id is the customers table key. Using SET SQL_SAFE_UPDATES = 0; is not an option. I also tried using WHERE c1.id > 0 to no avail.

Note that I tried to run the query manually changing SET SQL_SAFE_UPDATES = 0;, the query it's working as expected but this is supposed to be an automatic process.

So my options are:

  1. Using a single query, SQL_SAFE_UPDATES can't be used.
  2. Using Python cursors, SQL_SAFE_UPDATES can be used. (see SIDE NOTE)
  3. [insert an option here]
  4. (I don't want to do this) Iterate over each record (using the Python ORM) and update the record. This is stupid and will take forever.

UPDATE

Also tried:

  • Using the same ID

    UPDATE
  ...
WHERE c1.id = c1.id ;

  • Adding a huge limit after each subquery (as suggested by @Akina):

    UPDATE customers AS c1
INNER JOIN (
  SELECT id, DATEDIFF(NOW(), agg.cdt) AS acdt
  FROM customers
  INNER JOIN
    (
      SELECT c.id AS cid, max(o.datetime) as cdt
      FROM customers AS c
      LEFT JOIN orders o ON o.customer_id = c.id
      WHERE o.state = 20
      GROUP BY c.id
      LIMIT 100000000
    ) AS agg ON customers.id = agg.cid
  WHERE account_type IN (1, 2)
    AND deleted = 0
    AND (account_management_state IN (0, 1, 2) OR account_management_state IS NULL)
  LIMIT 100000000
) AS c2 ON c1.id = c2.id
SET c1.account_management_state = CASE
  WHEN c2.acdt <= 90 THEN 0
  WHEN c2.acdt >= 91 AND c2.acdt <= 360 THEN 1
  WHEN c2.acdt > 360 OR c2.acdt IS NULL THEN 2
  END
WHERE c1.id = c2.id;

  • A combination of both IDs:

    UPDATE
  ...
WHERE c1.id > 0 and c2.id > 0;

None of them are working. Still getting Error Code: 1175.

SIDE NOTE

This is part part of a Python/Flask process using MySQL python client and cursors. I can use SQL_SAFE_UPDATES as long as it's done from the Python cursor. This is not working:

  • Using different queries (not throwing an error, it just won't update anything):

    connection = db.get_conn()
cursor = connection.cursor()
cursor.execute('SET SQL_SAFE_UPDATES = 0;')
cursor.execute(query) # from the original query
cursor.execute('SET SQL_SAFE_UPDATES = 1;')

  • Using a single query (not throwing an error, it just won't update anything):

    connection = db.get_conn()
cursor = connection.cursor()
cursor.execute('''
    SET SQL_SAFE_UPDATES = 0;
    UPDATE ...;
    SET SQL_SAFE_UPDATES = 1;
''')

  • Using BEGIN .. END (thought I saw the light, but no), gettting a ProgrammingError:

    connection = db.get_conn()
cursor = connection.cursor()
cursor.execute('''
    BEGIN
        SET SQL_SAFE_UPDATES = 0;
        UPDATE ...;
        SET SQL_SAFE_UPDATES = 1;
    END
''')
César
  • 9,939
  • 6
  • 53
  • 74
  • 2
    This warning came because you are using non key column to search row and update. If you exact ID or key column then this will not come. – Avinash Dalvi May 29 '20 at 18:32
  • @aviboy2006 ah! You mean inside the first `INNER JOIN`, right? – César May 29 '20 at 18:36
  • yes. This is error for safety warning to avoid updating wrong rows. – Avinash Dalvi May 29 '20 at 18:36
  • I'm not sure how to tackle this problem, took me a while just to build this query, lol – César May 29 '20 at 19:33
  • see this two option https://stackoverflow.com/questions/59842134/mysql-you-are-using-safe-update-mode-and-you-tried-to-update-a-table-without-a-w and https://stackoverflow.com/questions/47761316/mysql-update-with-a-subquery-in-safe-update-mode – Avinash Dalvi May 29 '20 at 19:38
  • 1
    Try to add `LIMIT` clause to each (sub)query with the limit over the rows amount. – Akina May 29 '20 at 19:43
  • Sorry guys, none of them are working. Updated the question. – César May 29 '20 at 20:00
  • what is error you are getting same one ? – Avinash Dalvi May 29 '20 at 20:32
  • @aviboy2006 yes, same error – César May 29 '20 at 20:32
  • just add where cases for the id that do nothing. WHERE id IS NOT NULL Should do the trick I assume.(also make sure to add a not why you have this idiotic where, otherwise you will for sure remove it when cleaning up the code in the future ;) – Griffin May 29 '20 at 22:32

1 Answers1

0

I know it has been a long time, but maybe has the same issue as yours and mine today. I have solved it by committing my changes before closing my connection just add this before you close the connection "cnx.commit()" hope this can help out

Mohamed Alezzabi
  • 1