Click here to Skip to main content
12,501,833 members (50,214 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Posted 10-Jan-13 0:18am
Kunjammu1.1K
Updated 10-Jan-13 0:35am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  
Comments
Kunjammu 10-Jan-13 23:43pm
   
Hi. its not worked for me . let me elaborate my question. i deleted a row from this table manually. i am using a wpf usercontrol as my userinterface. if i click on a button. the copies should update. i am using a storedprocedure for this. can u plz tell how can do this?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

You can Try with trigger,.. That will be more efficient and the best way to update
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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
  Permalink  
Comments
Kunjammu 10-Jan-13 23:44pm
   
Hi. its not worked for me . let me elaborate my question. i deleted a row from this table manually. i am using a wpf usercontrol as my userinterface. if i click on a button. the copies should update. i am using a storedprocedure for this. can u plz tell how can do this?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160919.1 | Last Updated 10 Jan 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100