How can I correct pymysql statement?-CodePudding

I have a pymysql update query that I want to work. It keeps throwing me an error.


    mydb = mysql.connector.connect(
    host="*****",
    user="****",
    password="****",
    database="database",
    port="****"
    )
    mycursor = mydb.cursor()
    mycursor.execute ("""
    UPDATE table1
    SET BUGS=%s, CODE_SMELLS=%s, VULNERABILITIES=%s
    WHERE username = %s
    AND time_created = (SELECT MAX(time_created) FROM table1
    )
    """, (bugs, code_smells, vulnerabilities, username))
    mydb.commit()
    mydb.close()

mysql.connector.errors.DatabaseError: 1093 (HY000): You can't specify target table 'table1' for update in FROM clause

CodePudding user response：

Try

UPDATE table1
SET BUGS=%s, CODE_SMELLS=%s, VULNERABILITIES=%s
WHERE username = %s
ORDER BY time_created DESC
LIMIT 1

or use a subquery that creates a temp table. So you will not update directly the table you are selecting from.

UPDATE table1
SET BUGS=%s, CODE_SMELLS=%s, VULNERABILITIES=%s
WHERE username = %s
AND time_created = 
(
  SELECT * FROM
  (
     SELECT MAX(time_created) FROM table1
  ) tmp
)