Update a column based on list-CodePudding

I am using MSSQL. I have a table students.

| id |name |attendance|status|
|----|-----|----------|------|
| 1  |Tom |present    |Due   |
| 2  |Jerry|absent    |Due   |
| 3  |Tim  |present   |Due   |
| 4  |Aan  |present   |Due   |
| 5  |Niya |absent    |synced|

I want to fetch id, name and attendance from students, whose status is 'Due'. After fetching this data, I want to update the status to 'sync-in-progress' based on the fetched id's. Is there a way to update the status without using the loop. This is my code:

# Select  id,name and attendance data from students table whose status  is due. 
cursor.execute("""SELECT id,name,attendance FROM students
                   WHERE status = 'DUE'""")
 data = cursor.fetchall()

  # List of id's selected.
 log_id = [i[0] for i in data]

  # Update the status of the selected id's to sync-in-progress.
 cursor.execute("""UPDATE students
                   SET  status = 'sync-in-progress'
                   WHERE id IN(?)""", (log_id,))
 connection.commit()

CodePudding user response：

You can done it with single query:

UPDATE students
SET  status = 'sync-in-progress'
WHERE status = 'DUE'

CodePudding user response：

You can do it like this.

# Select id, name and attendance data from students table whose status  
# is due. 
cursor.execute("""SELECT id,name,attendance FROM students 
                  WHERE status = 'DUE'""")
 data = cursor.fetchall()
 
# List of id's selected. 
log_id = [i[0] for i in data]

  # Convert the list into tuple.
 log_id = tuple(log_id) 

 # Update the status of the selected id's to sync-in-progress.
 cursor.execute("""UPDATE students 
                  SET  status = 'sync-in-progress'
                   WHERE id IN{};""".format(log_id))
 connection.commit()