MySQL在一个查询中选择并更新 [英] MySQL select and update in one query

问题描述

我正在尝试从MySQL中选择一个值(total_points)，然后添加到局部变量(new_points)并更新SAME查询中的total_points，有人知道这是否可行...

I'm trying to select a value from MySQL (total_points) then add to a local variable (new_points) and update total_points in the SAME query, does anyone know if this is possible...

我目前有.

cursor = database.cursor() 
cursor.execute("""SELECT total_points FROM g_ent WHERE e_id =%s AND user =%s;
UPDATE total_points = (total_points + %s)"""
,(e_id, user_name, new_points))
database.commit()    

推荐答案

问题是您的SQL语法不正确.查询应为:

The issue is that your SQL syntax is not correct. The query should be:

UPDATE g_ent 
SET total_points = total_points + %s
WHERE e_id = %s AND user = %s;

完整的示例为:

cursor = database.cursor() 
cursor.execute("""UPDATE g_ent 
                  SET total_points = total_points + %s
                  WHERE e_id = %s AND user = %s;""",
               (new_points, e_id, user_name)) # order of params revised
database.commit()

请注意，查询参数的顺序已修改.

Please note that the order of the query parameters was revised.

这篇关于MySQL在一个查询中选择并更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！