Update field in batches

• DataAnalyst011

  SSCarpal Tunnel

  Points: 4035

  More actions

  July 27, 2017 at 11:30 am

  #402369

  Hi All,

  I'm attempting to update a field in batches due to some restrictions. Here is a sample of the structure:

  CREATE TABLE ChgEmail
(
    tID int identity(1,1),
    email varchar(50)
    PRIMARY KEY(tID)
)

  INSERT INTO ChgEmail(email)
VALUES ('1@email.com'),
    ('2@email.com'),
    ('3@email.com'),
    ('4@email.com'),
    ('5@email.com'),
    ('6@email.com'),
    ('7@email.com'),
    ('8@email.com'),
    ('9@email.com'),
    ('10@email.com'),
    ('11@email.com'),
    ('12@email.com')

  I was hoping to use something like NTILE to split this up into 4 batches. In each batch I need to update the email to 'noemail@email.com' and commit the transaction. Then, move to the next batch. Any suggestions on how to do this? 

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  July 27, 2017 at 12:08 pm

  #1953079

  
DROP TABLE IF EXISTS dbo.ChgEmail;
GO
CREATE TABLE dbo.ChgEmail
  (
      tID int identity(1,1),
      email varchar(50),
      PRIMARY KEY(tID)
  );
  GO
  INSERT INTO ChgEmail(email)
  VALUES ( '1@email.com' ) 
      ,  ( '2@email.com' ) 
      ,  ( '3@email.com' ) 
      ,  ( '4@email.com' ) 
      ,  ( '5@email.com' ) 
      ,  ( '6@email.com' ) 
      ,  ( '7@email.com' ) 
      ,  ( '8@email.com' ) 
      ,  ( '9@email.com' ) 
      ,  ( '10@email.com' )
      ,  ( '11@email.com' )
      ,  ( '12@email.com' ) ;
  GO
  SELECT
   ce.tID, ce.email
  FROM
   dbo.ChgEmail ce;
  GO
  DECLARE @int int = (SELECT Min(tID)-1 FROM dbo.ChgEmail);
  WHILE @int < (SELECT Max(tID) FROM dbo.ChgEmail)
  BEGIN
   WITH ce AS
   (
   SELECT
    tID, email
   FROM
    dbo.ChgEmail
   ORDER BY tID
   OFFSET @int ROW
   FETCH NEXT 2 ROW ONLY
   )
   UPDATE ce
    SET email = 'noemail@'+Right(email, Len(email)-CharIndex('@',email))
   WHERE ce.tID BETWEEN @int AND @int+2;
   SET @int += 2;
  END
  GO
  SELECT
   ce.tID, ce.email
  FROM
   dbo.ChgEmail ce;
  GO
  DROP TABLE dbo.ChgEmail;
  GO