Using while loop in Sql

Question

1.00/5 (1 vote)

See more:

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

Posted 10-Jan-13 19:46pm

Minnu Sanju

Updated 12-Jan-13 11:05am

Maciej Los

v6

Add a Solution

Comments

Reza Alipour Fard 11-Jan-13 3:19am

your question has ambiguity.
you need update copies column? or delete some rows?
Do you need to group by table by MasterAccessionNo?
Please give more information.

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

__TR__ · Answer 1 · 2013-01-10T19:49:00

Solution 1

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[^]

Posted 10-Jan-13 19:49pm

__TR__

Updated 10-Jan-13 19:52pm

v2

Comments

Maciej Los 12-Jan-13 17:22pm

Good answer, my 5!

Maciej Los · Answer 2 · 2013-01-12T11:22:00

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

SQL

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.

SQL

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

Virander Sirohi · Answer 3 · 2013-01-10T19:57:00

SQL

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

Virander Sirohi · Answer 4 · 2013-01-10T19:58:00

Solution 3

SQL

UPDATE dbo.YourTableName
SET Copies =  --What ever u want
WHERE AccessionNo =  MasterAccessionNo

this is another way without loop.

Posted 10-Jan-13 19:58pm

Virander Sirohi

Comments

Minnu Sanju 11-Jan-13 2:14am

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

Using while loop in Sql

4 solutions

Solution 1

Solution 5

Solution 2

Solution 3

Add your solution here

Preview 0