Sql query for getting the count and updation

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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

Kunjammu

Updated 10-Jan-13 0:35am

Aarti Meswania

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Tejal S · Answer 1 · 2013-01-10T01:22:00

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

Posted 10-Jan-13 1:22am

Tejal S

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?

Tejal S · Answer 2 · 2013-01-10T01:25:00

Solution 2

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

Posted 10-Jan-13 1:25am

Tejal S

Virander S · Answer 3 · 2013-01-10T06:37:00

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 :)

SQL

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