[SOLVED] how to run update sql query based on select query results using python

how to run update sql query based on select query results using python

I am trying to update a row in an sql table based on results of a select query using python. How to add same so that if results found in select query we should be able to run update query and print as list updated, if not exit saying no result found to update table

cursor = conn.cursor()
cursor.execute(
    "select * from student where email = 'xyz.com'"
)
student = cursor.fetchall()
print(student)

for row in student:
    cursor.execute(" Update student set value = 0 where email = 'xyz.com'")

Solution

You don't need to do a separate SELECT, you can just use an OUTPUT clause to have your UPDATE statement return the value(s) from the row(s) that are updated. For example, with pyodbc:

sql = """\
UPDATE student SET value = 0
OUTPUT INSERTED.student_number
WHERE email = 'xyz.com' AND (value <> 0 OR value IS NULL)
"""
student_numbers = crsr.execute(sql).fetchall()
print(student_numbers)  # [(1001, ), (1003, )]