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

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 
 FROM  YT  INNER   JOIN （
 SELECT  MasterAccessionNo，COUNT（MasterAccessionNo） AS 复制
 FROM  YourTable 
） AS  YT1  ON  YT.AccessionNo = YT1.MasterAccessionNo 

ockquote>

  DECLARE   @ TableVar   AS   TABLE  
（
 ID  INT   IDENTITY （ 1 ， 1  ），
 AccessionNo  INT ，
 MasterAccessionNo  INT ，
复制 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 （ 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