获取计数和更新的SQL查询 [英] Sql query for getting the count and updation

问题描述

我有一张如下表格

 AccNo MAccNo复制
 
 1 1 2 
 2 1 1 
 3 3 3 
 4 3 1 
 5 3 1 
 6 6 4 
 7 6 1 
 8 6 1 
 9 6 1 

具有相同AccNo和MAccNo的行是主记录



如果副本= 2，则有2条记录相同的MaccNo。如果Copies = 4，则有4条记录具有相同的MaccNo，依此类推。



我手动从这张表中删除了一条记录。然后副本应在主记录中扣除一个。我怎样才能做到这一点。 Plz帮帮我



在此先感谢

Kunjammu

解决方案

选择MaccNo ，伯爵（MaccNo）作为副本

来自表格

group by MAccNo



更新a

SET a.Copies = a.Copies - 1

FROM tableA a WITH（NOLOCK）

JOIN tableA b WITH（NOLOCK）

ON a.AccNo = b.AccNo

WHERE b.MAccNo = a.AccNo

AND b.AccNo = 9 - （待删除）



- 然后



从tablA删除，其中AccNo = 9

您可以尝试使用触发器，..这将更有效，更新的最佳方式

试试这个我相信这会有所帮助。创建触发器后，您可以随意删除任何内容。你会看到变化:)

  IF   OBJECT_ID （'  dbo.DeleteTriggerOnTableA'，'  TR'） IS   NOT   NULL  
  DROP   TRIGGER  dbo.DeleteTriggerOnTableA; 
  GO  
  CREATE   TRIGGER  dbo.DeleteTriggerOnTableA 
  ON  dbo.TableA 
 AFTER  DELETE  
  AS  
 
 更新 tbl 
  SET  tbl.Copies = tbl.Copies  -   1  
  FROM  dbo.tableA tbl  WITH （ NOLOCK ）
  JOIN  DELETED del  WITH （ NOLOCK ）
  ON  tbl.MAccNo = del.MAccNo 
  WHERE  tbl.MAccNo = tbl.AccNo 
 
  END  
  GO  

Hi,

I have a table like below

AccNo    MAccNo   Copies

1        1        2
2        1        1
3        3        3
4        3        1
5        3        1
6        6        4
7        6        1
8        6        1
9        6        1

the row with same AccNo and MAccNo is master record

if copies=2, there are 2 records same MaccNo. if Copies=4, there are 4 records with same MaccNo and so on.

i deleted a record from this table manually. then Copies should deducted by one in the master record. How can i do this. Plz help me

Thanks in Advance
Kunjammu

解决方案

select MaccNo,Count(MaccNo) as copies
From Table
group By MAccNo

Update a
SET a.Copies = a.Copies – 1
FROM tableA a WITH(NOLOCK)
JOIN tableA b WITH(NOLOCK)
ON a.AccNo = b.AccNo
WHERE b.MAccNo = a.AccNo
AND b.AccNo = 9 -- (to be deleted)

--Then

DELETE from tablA where AccNo = 9

You can Try with trigger,.. That will be more efficient and the best way to update

Try this I am sure this will help. After creating trigger you can delete whatever you want and however you want. you will see the change :)

IF OBJECT_ID ('dbo.DeleteTriggerOnTableA','TR') IS NOT NULL
    DROP TRIGGER dbo.DeleteTriggerOnTableA;
GO
CREATE TRIGGER dbo.DeleteTriggerOnTableA
ON dbo.TableA
AFTER DELETE 
AS

   UPDATE tbl
      SET tbl.Copies = tbl.Copies – 1
   FROM dbo.tableA tbl WITH(NOLOCK)
   JOIN DELETED del WITH(NOLOCK)
      ON tbl.MAccNo = del.MAccNo
   WHERE tbl.MAccNo = tbl.AccNo

END
GO

这篇关于获取计数和更新的SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！