在Sql中使用while循环 [英] Using while loop in Sql

问题描述

我有一张桌子。它有三列：AccessionNo，MasterAccessionNo，Copies

我必须在具有AccessionNo = MasterAccessionNo的行中更新Copies列。



这是我的桌子：

 Id AccessionNo MasterAccessionNo副本
 1 001 001 3 
 2 002 001 1 
 3 003 001 1 
 4 004 004 2 
 5 005 004 1 
 

现在如果我用Id手动删除一行从表中2，然后副本应从3更新到2，其中 AccessionNo = MasterAccessionNo 。如果我要删除更多记录，则应更新相应的副本，检查记录 AccessionNo = MasterAccessionNo





我该怎么做

解决方案

这是一个非常基本的问题。请参阅以下链接以获取更新语句语法

SQL UPDATE语句 [ ^ ]

更新SQL Server基础知识 [ ^ ]

更多链接此处 [ ^ ]



如果您正在寻找while循环示例

请参考带有BREAK和CONTINUE的WHILE循环的简单示例 [ ^ ]

首先，您不需要在表格中存储副本。为什么？因为您总是可以在 SELECT 语句中获取它。

  SELECT  MasterAccessionNo，COUNT（MasterAccessionNo） AS 复制
  FROM  YourTable 

但是如果您仍想在表中存储Copies，请使用UPDATE命令作为_TR_写入。

  UPDATE  YourTable YT 
  SET  
 YT.Copies = YT1.Copies 
 < span class =code-keyword> FROM  YT  INNER   JOIN （
  SELECT  MasterAccessionNo，COUNT（MasterAccessionNo） AS 复制
  FROM  YourTable 
） AS  YT1  ON  YT.AccessionNo = YT1.MasterAccessionNo 

  DECLARE   @ TableVar   AS   TABLE  
（
 ID  INT   IDENTITY （ 1 ， 1  ），
 AccessionNo  INT ，
 MasterAccessionNo  INT ，
复制< span class =code-keyword> INT  
）; 
 
  DECLARE   @ CurrentIndex   AS   INT  =  1 ; 
  DECLARE   @ TotalCount   AS   INT ; 
 
  SELECT   @ TotalCount  = MAX（ID）
  FROM   @ TableVar  
 
  INSERT   INTO   @ TableVar  
  SELECT  AccessionNo，MasterAccessionNo，复制
  FROM  dbo.YourTableName  WITH （ NOLOCK ）
  WHERE  AccessionNo = MasterAccessionNo; 
 
  WHILE （ @ TotalCount > =  @ CurrentIndex ）
  BEGIN  
 
  UPDATE  ytn 
  SET  ytn.Copies =   -   你想要什么 
  FROM  dbo.YourTableName ytn  WITH （< span class =code-keyword> NOLOCK ）
  JOIN   @ TableVar  tv 
  ON  tv.AccessionNo = ytn.AccessionNo 
  WHERE  tv.ID =  @ CurrentIndex  
 
  END  

Hi,
I have a table. It has three columns: AccessionNo, MasterAccessionNo, Copies
I have to update the Copies column in rows having AccessionNo=MasterAccessionNo.

This is my table:

Id     AccessionNo    MasterAccessionNo   Copies
 1       001              001               3
 2       002              001               1
 3       003              001               1
 4       004              004               2
 5       005              004               1

Now if i am deleting a row manually with Id 2 from the table, then the copies should be updated from 3 to 2 where AccessionNo=MasterAccessionNo. If i am deleting more records then the corresponding copies should be updated checking that records AccessionNo=MasterAccessionNo


How can i do this

解决方案

This is a very basic question. Refer the below links for Update statment syntax
SQL UPDATE Statement[^]
UPDATE Basics in SQL Server[^]
More links here[^]

If you are looking for while loop example
refer Simple Example of WHILE Loop with BREAK and CONTINUE[^]

First of all, you don''t need to store "copies" in your table. Why? Because you can always get it in SELECT statement.

SELECT MasterAccessionNo, COUNT (MasterAccessionNo) AS Copies
FROM YourTable

But if you still want to store "Copies" in your table, use UPDATE command as _TR_ wrote.

UPDATE YourTable YT
SET
    YT.Copies = YT1.Copies
FROM YT INNER JOIN (
    SELECT MasterAccessionNo, COUNT(MasterAccessionNo) AS Copies
    FROM YourTable
    ) AS YT1 ON YT.AccessionNo = YT1.MasterAccessionNo

DECLARE @TableVar AS TABLE
(
   ID INT IDENTITY(1,1),
   AccessionNo INT, 
   MasterAccessionNo INT, 
   Copies INT
);

DECLARE @CurrentIndex AS INT = 1;
DECLARE @TotalCount AS INT;

SELECT @TotalCount = MAX(ID)
FROM @TableVar

INSERT INTO @TableVar
SELECT AccessionNo, MasterAccessionNo, Copies
FROM dbo.YourTableName WITH(NOLOCK)
WHERE AccessionNo =  MasterAccessionNo;

WHILE(@TotalCount >= @CurrentIndex)
BEGIN

  UPDATE ytn
     SET ytn.Copies = --What ever u want
  FROM dbo.YourTableName ytn WITH(NOLOCK)
  JOIN @TableVar tv
     ON tv.AccessionNo = ytn.AccessionNo
  WHERE tv.ID = @CurrentIndex

END

这篇关于在Sql中使用while循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！