none
Need help with updating million rows in a table RRS feed

  • Question

  • Hi,

    I have a million rows where I have to update paths in a  column. For Example my current path column has 'Computer/Servers/a1/a2....' as value. The million rows will have different values as  '%/Servers/a2/a3....' . So what i have to do is Remove all characters till 'Servers' . The new updated path should be '/a1/a2...' . Can anyone suggest the most efficient way to handle this scenario ?

    Thursday, March 16, 2017 2:51 PM

All replies

  • I do it in batches.

    For example - here is a delete I did recently

    declare @rowcount int
    set rowcount 1000
    DELETE MailQueue from Members  
    inner join MailQueue on Members.MemberId = MailQueue.MemberId
    WHere MailQueue.MailingId = 234 and Status = 'X'
    and [date entered] <= '6/1/2016'​
    select @rowcount=@@ROWCOUNT
    while @rowcount=1000
    begin
    DELETE MailQueue from Members  
    inner join MailQueue on Members.MemberId = MailQueue.MemberId
    WHere MailQueue.MailingId = 234 and Status = 'X'
    and [date entered] <= '6/1/2016'​
    select @rowcount=@@ROWCOUNT
    end
    What we need to do is figure out what rows have been updated. Could you add an int column to your table and place an index on it and set it to 1 so that you can include this in your where clause? ie
    declare @rowcount int
    set rowcount 1000
    update Members  set path =replace(path,'c:\temp','d:\temp'),updated=1
    inner join MailQueue on Members.MemberId = MailQueue.MemberId
    WHere MailQueue.MailingId = 234 and Status = 'X'
    and [date entered] <= '6/1/2016'​
    select @rowcount=@@ROWCOUNT
    while @rowcount=1000
    begin
    update Members  set path =replace(path,'c:\temp','d:\temp'), updated=1
    inner join MailQueue on Members.MemberId = MailQueue.MemberId
    WHere MailQueue.MailingId = 234 and Status = 'X'
    and [date entered] <= '6/1/2016'​ and updated is null
    select @rowcount=@@ROWCOUNT
    end


     



    Thursday, March 16, 2017 6:12 PM
    Moderator
  •  update mytable
     set path = substring(path,PATINDEX('%Servers%',path)+7,datalength(path))  --7 is the length of 'Servers'

    Thursday, March 16, 2017 6:40 PM
  • -- can you try this 
    
    UPDATE [tbl]
    SET [Path]= SUBSTRING( [Path] , CHARINDEX('/Servers/',[Path] ) + 8,  LEN([Path])) ;

    Saturday, March 18, 2017 4:39 PM