使用连续编号更新 SQL [英] Update SQL with consecutive numbering

问题描述

我想更新一个从 1 开始连续编号的表.更新有一个 where 子句，所以只有满足该子句的结果才会被重新编号.我可以在不使用临时表的情况下有效地完成这项工作吗?

I want to update a table with consecutive numbering starting with 1. The update has a where clause so only results that meet the clause will be renumbered. Can I accomplish this efficiently without using a temp table?

推荐答案

这可能取决于您的数据库，但这里有一个涉及使用变量的 MySQL 5 解决方案:

This probably depends on your database, but here is a solution for MySQL 5 that involves using a variable:

SET @a:=0;
UPDATE table SET field=@a:=@a+1 WHERE whatever='whatever' ORDER BY field2,field3

您可能应该编辑您的问题并指明您使用的是哪个数据库.

You should probably edit your question and indicate which database you're using however.

我找到了一个解决方案，使用 T-SQL for SQL服务器.它与MySQL方法非常相似:

I found a solution utilizing T-SQL for SQL Server. It's very similar to the MySQL method:

DECLARE @myVar int
SET @myVar = 0

UPDATE
  myTable
SET
  @myvar = myField = @myVar + 1

这篇关于使用连续编号更新 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！