Solved

Updating Multiple rows

Posted on 2010-08-23
6
372 Views
Last Modified: 2012-05-10
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
Comment
Question by:Salman_Ali
6 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 405 total points
ID: 33507523
where a.ProductID=b.ppid
and a.ddid <> b.ddid
0
 
LVL 7

Expert Comment

by:alphaau
ID: 33508217
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33508636
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33508657
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33509433

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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33509476
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now