Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to select a value from each sql row to use in a function and then update each row

I need to update each row in a table with a value returned by a function. This function uses a separate columns value of the same row. I understand how to select a columns value from a row and how to update one but not how to do both for every row in a table.

For example if I had a table with employees information like their birthdate, how would I use python to take that birthdate, run it through a function to determine their age and then update that specific row’s age column with that determined age. I know how to do this for one row, but not for every row in the table that does not yet have a value for their age.

cursor = connection.cursor()


for row in cursor.execute('SELECT * FROM Table' WHERE Birthdate != ''):
   sql_update_query = """Update name-of-table set age = %s where 
   age = '' """
   age = CurrentYear - yearBorn
   input = (age)
   cursor.execute(sql_update_query, input)

Sorry for the sloppy code but here is an example of what I have tried to do ^

Tags:

2

Answers


  1. 0

    You need to make the update specific to the current row of the loop. Use the primary key of the table for this — I’ve assumed it’s called id in my code below.

    sql_update_query = """Update employees set age = %s where id = %s"""

for id, birthdate in cursor.execute('SELECT id, BirthDate FROM employees' WHERE Birthdate != ''):
   age = calculate_age(birthdate)
   cursor.execute(sql_update_query, (age, id))
    Login or Signup to reply.
  2. 0 
    cursor = connection.cursor()

current_year = datetime.datetime.now().year

query = "UPDATE your_table SET age = {} - (YEAR(birthdate) + IF(MONTH(birthdate) > MONTH(CURRENT_DATE), 1, 0)) WHERE age = ''"
cursor.execute(query.format(current_year))
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search