如何使用mysql顺序更新数据库 [英] how to update database sequentially using mysql

问题描述

我有一个很长的数据库列表，我想按名称组更新一个字段.我想将前30个值更新为'1'，然后将30个值更新为'2'，依此类推，直到最后

I have a long list from database which i want to update a field by name group. i want to update the first 30 to value '1' next 30 to value '2' and so on until the end

关于如何进行操作的任何想法

Any idea on how to go about it

$sql = mysqli_query($conn,
   "UPDATE `tablename` SET `group` = '$value' WHERE id IN (SELECT id FROM tablename ORDER BY id desc LIMIT 0, 30)");

推荐答案

如果您的更新是一次性的(并且不需要对其进行优化，例如，一次查询即可完成所有操作)，则只需重复执行以下查询，直到完成:

If your update is a one-time thing (and don't need to optimize it, e.g. do it all in one query), you can simply repeat the following query until you are finished:

update `tablename` 
SET `group` = '$value' 
where `group` is null
order by id desc
limit 30;

所有组值都必须为空(或其他一些未使用的值)，例如如果不是，请先使用update tablename SET group = null.

All group-values have to be null (or some other unused value) to begin with, so e.g. use update tablename SET group = null first, if they aren't.

您可能想在事务中完成所有操作.

And you probably want to do it all in a transaction.

这篇关于如何使用mysql顺序更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！