Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

Updating Multiple rows

I want update 12 carore records. I want to update from record 1 to 5000, then from 5001 to 10000, then from 10001 to 15000 and so on. How to do it in a loop. Query is here but it always update first 5000. How to update next 5000 record from 50001 to 10,000 and then from 10,001 to 15000.
SET ROWCOUNT 5000
WHILE (1=1) BEGIN
   BEGIN TRANSACTION

update newformularydtl
set newformularydtl.ddid=b.ddid from newformularydtl a,Medispan.dbo.mmw_drug_pack b
where a.ProductID=b.ppid
   IF @@ROWCOUNT = 0
   BEGIN
      COMMIT TRANSACTION
      BREAK
   END
   COMMIT TRANSACTION
END
0
Salman_Ali
Asked:
Salman_Ali
1 Solution
 
cyberkiwiCommented:
where a.ProductID=b.ppid
and a.ddid <> b.ddid
0
 
Alpha AuCommented:
any condition to indicate the record is updated or not?

like what cyberkiwi suggested.
SET ROWCOUNT 5000
WHILE (1=1) BEGIN
   BEGIN TRANSACTION

update newformularydtl
set newformularydtl.ddid=b.ddid 
from newformularydtl a,Medispan.dbo.mmw_drug_pack b
where a.ProductID=b.ppid
and a.ddid<>b.ddid 
   IF @@ROWCOUNT = 0
   BEGIN
      COMMIT TRANSACTION
      BREAK
   END
   COMMIT TRANSACTION
END

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
and, in case that the productid could be null, you need to check that case also.
with isnull, putting a "default value" that cannot be in the normal range, but in the same data type.
so, if the field productid is numerical, you might not have "0" as possible value:

SET ROWCOUNT 5000
WHILE (1=1) BEGIN
   BEGIN TRANSACTION

update newformularydtl
set newformularydtl.ddid=b.ddid 
from newformularydtl a,Medispan.dbo.mmw_drug_pack b
where a.ProductID=b.ppid
and isnull(a.ddid, 0) <> isnull(b.ddid , 0)
   IF @@ROWCOUNT = 0
   BEGIN
      COMMIT TRANSACTION
      BREAK
   END
   COMMIT TRANSACTION
END

Open in new window

0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
cyberkiwiCommented:
Well, while we are improving.. this caters for all cases and has no condition that 0 cannot be in the data.

SET ROWCOUNT 5000
WHILE (1=1) BEGIN
   BEGIN TRANSACTION

update newformularydtl
set ddid=b.ddid
from Medispan.dbo.mmw_drug_pack b
where newformularydtl.ProductID=b.ppid
  and ((newformularydtl.ddid <> b.ddid)
       or (newformularydtl.ddid is null and b.ddid is null))
   IF @@ROWCOUNT = 0
   BEGIN
      COMMIT TRANSACTION
      BREAK
   END
   COMMIT TRANSACTION
END
0
 
Bhavesh ShahLead AnalysistCommented:

Mr.Genius(angelIII) & Mr.Sage(Cyberkiwi)

Is it not good if author makes one perfect index update the data one shot.

Please comment on it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
for the record:
> 12 carore (aka crore) http://en.wikipedia.org/wiki/Crore
seems to be 12 x 10 million rows.

to update those in 1 shot can take some time, especially if most rows have to be updated.
if most rows are already ok, the update should indeed be done in 1 single go, but ensuring that rows that are already ok arenot updated (without the rowcount):
update newformularydtl
set ddid=b.ddid
from Medispan.dbo.mmw_drug_pack b
where newformularydtl.ProductID=b.ppid
  and ((newformularydtl.ddid <> b.ddid)
       or (newformularydtl.ddid is null and b.ddid is null))

Open in new window

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now