MSSQL如何无需循环基于查询ID列表批量更新表字段

场景说明 #

当前使用MSSQL数据库，存在students表，样例数据如下：

需求为：查询表中status为Due的记录的id、name、attendance字段，再将查询到的对应记录的status更新为sync-in-progress，要求不使用循环实现。
原有实现代码如下：

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

最优实现方案（单SQL原子操作，无循环） #

原有代码存在两个明确问题：

• 直接将列表传入IN(?)的单个占位符无法被MSSQL驱动正确解析，会执行失败
• 先查询再更新的两步操作存在并发风险，两次操作间隙如果有其他进程修改了对应记录的状态，会出现数据不一致

MSSQL原生支持OUTPUT子句，可以在更新数据的同时直接返回被更新行的指定字段，单条SQL即可完成所有需求，全程无循环，原子性更强，性能更好：

# 单条SQL同时完成状态更新、返回所需字段，无需额外查询、无需循环
cursor.execute("""
UPDATE students
SET status = 'sync-in-progress'
OUTPUT INSERTED.id, INSERTED.name, INSERTED.attendance
WHERE status = 'Due'
""")
# 直接获取被更新记录的目标字段，和原有逻辑查询得到的data内容完全一致
data = cursor.fetchall()
connection.commit()

兼容先查后更场景的无循环实现 #

如果业务逻辑要求必须先拿到查询结果做其他非数据库操作，再执行更新，也不需要写循环，只需要根据查询到的ID数量动态生成IN子句的占位符即可：

# 第一步查询逻辑不变
cursor.execute("""SELECT id,name,attendance FROM students
                  WHERE status = 'Due'""")
data = cursor.fetchall()
log_id = [i[0] for i in data]

if log_id:
    # 动态生成对应数量的参数占位符，无循环
    placeholders = ','.join(['?'] * len(log_id))
    update_sql = f"""UPDATE students
                    SET status = 'sync-in-progress'
                    WHERE id IN ({placeholders})"""
    # 直接传入ID列表作为参数，驱动会自动完成参数绑定
    cursor.execute(update_sql, log_id)
    connection.commit()

注意：原有查询语句中status = 'DUE'的大小写和表中实际存储的Due不一致，若数据库使用大小写敏感排序规则会导致查询结果为空，建议和实际存储值保持一致。

内容的提问来源于stack exchange，提问作者Ajdal