MySQL select and update in one query

Question

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...

I currently have.,

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

Solution 1

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;

The full example would then be:

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.

Solution 2

UPDATE g_ent
SET total_points = total_points + %s
Where e_id = %s 
  AND user = %s