如何在循环中执行此 MySql 语句

发布于 2025-01-17 02:19:10 字数 383 浏览 5 评论 0原文

我会更复杂地编写我的命令行语句。如何使用循环来完成此操作？

     update refs set custom_7 = '';
     update refs set custom_7='1' where custom_7 = '' limit 220 ;
     update refs set custom_7='2' where custom_7 = '' limit 220 ;
     update refs set custom_7='3' where custom_7 = '' limit 220 ;
     ...
     update refs set custom_7='100' where custom_7 = '' limit 220 ;

多谢。

原文

I would write my cmd line statements more sophistically. How can this be done by using a loop?

     update refs set custom_7 = '';
     update refs set custom_7='1' where custom_7 = '' limit 220 ;
     update refs set custom_7='2' where custom_7 = '' limit 220 ;
     update refs set custom_7='3' where custom_7 = '' limit 220 ;
     ...
     update refs set custom_7='100' where custom_7 = '' limit 220 ;

thanks a lot.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夏夜暖风 2025-01-24 02:19:10

如果有一列（例如 id）定义了要更新行的行顺序，请使用 ROW_NUMBER() 窗口函数对行进行排名并连接到表：

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM refs)
UPDATE refs r
INNER JOIN cte c ON c.id = r.id
SET r.custom_7 = (c.rn - 1) DIV 220 + 1
WHERE c.rn <= 100 * 220; -- remove the WHERE clause if there is actually no limit to the values of custom_7

如果没有像 id 这样的列，您可以从 OVER() 子句中删除 ORDER BY id代码>ROW_NUMBER()，但是然后行将被任意更新。

请参阅简化的演示。

If there is a column, like an id, that defines the order of the rows by which you want to update the rows, use ROW_NUMBER() window function to rank the rows and join to the table:

WITH cte AS (SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn FROM refs)
UPDATE refs r
INNER JOIN cte c ON c.id = r.id
SET r.custom_7 = (c.rn - 1) DIV 220 + 1
WHERE c.rn <= 100 * 220; -- remove the WHERE clause if there is actually no limit to the values of custom_7

If there is no column like the id, you may remove ORDER BY id from the OVER() clause of ROW_NUMBER(), but then the rows will be updated arbitrarily.

See a simplified demo.

回复收藏 0 原文

玉环 2025-01-24 02:19:10

您可以尝试这样的操作（请将datatype(length)替换为custom7的类型）

DECLARE @count INT;    
SET @count = 1;        
WHILE @count<= 100    
     BEGIN
   
          UPDATE refs SET custom_7 = CAST(@count AS **datatype(length)**) WHERE custom_7 = '' LIMIT 220;    
          SET @count = @count + 1;    
     END;

You can try something like this (please replace datatype(length) with the type of custom7)

DECLARE @count INT;    
SET @count = 1;        
WHILE @count<= 100    
     BEGIN
   
          UPDATE refs SET custom_7 = CAST(@count AS **datatype(length)**) WHERE custom_7 = '' LIMIT 220;    
          SET @count = @count + 1;    
     END;

回复收藏 0 原文

~没有更多了~