none
How to use cursor or CTE RRS feed

  • Question

  • Dear all,

    I need to execute the following :

    SELECT ID
    
    FROM mytable
    WHERE myField LIKE '%a'

    The query above return a list of record that match the pattern LIKE
    Then for each records found I need to execute the following update

    UPDATE myTable SET myField = REPLACE(myField, 'aaa', 'bbb')

    how can I use a loop using cursor or cte to do this ?

    regards

    Monday, September 9, 2019 3:21 PM

Answers

  • Hi 

    I am just referring to my first reply.

    Sample code looks as below

    DECLARE @Count int ,@batch int;
    SET @batch = 0;
    SELECT @Count = COUNT(1)
    FROM mytable
    WHERE myField LIKE '%a';
     
    WITH Cte as (
    SELECT ID,myField,ROW_NUMBER() OVER(ORDER BY ID) AS Rno
    FROM mytable
    WHERE myField LIKE '%a')
    
    
    WHILE (@batch < @Count)
    BEGIN
    	UPDATE Cte 
    	SET myField = REPLACE(myField, 'aaa', 'bbb')
    	WHERE Rno < 5000+@batch;
    
    	SET @batch = 5000+@batch
    END

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Tuesday, September 10, 2019 7:26 AM

All replies

  • Hi 

    There are couple of approaches to achieve the same. Hope the below code fulfills the need.

    Approach-1 (CTE)

    WITH Cte as (
    SELECT ID,myField
    FROM mytable
    WHERE myField LIKE '%a')
    
    UPDATE Cte SET myField = REPLACE(myField, 'aaa', 'bbb')

    Approach - Direct Update

    UPDATE mytable
    SET myField = REPLACE(myField, 'aaa', 'bbb')
    WHERE myField LIKE '%a'


    Thanks


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Monday, September 9, 2019 3:58 PM
  • You do not want to loop in SQL Server.  You want to do "set based operations".  Just change them all at once.

    Monday, September 9, 2019 4:02 PM
    Moderator
  • Dear all,

    I need to execute the following :

    SELECT ID
    
    FROM mytable
    WHERE myField LIKE '%a'

    The query above return a list of record that match the pattern LIKE
    Then for each records found I need to execute the following update

    UPDATE myTable SET myField = REPLACE(myField, 'aaa', 'bbb')

    how can I use a loop using cursor or cte to do this ?

    regards

    Good day wakefun,

    According to your description recursive CTE is not relevant at all. You can use a while loop directly or a cursor for the task but actually there is no reason to use any type of loop but simple UPDATE with filter.

    Why not recursive CTE?

    recursive CTE return single SET of result (tabular structure) and in your request you want to get only integer in the first execution (meaning this rows will have one column) and in the next execution you want to get a result with multiple columns. You cannot have one table which has rows with different number of columns.

    So what is the solution

    simple UPDATE with filter.

    If you can provide queries to create the table and to insert some sample data, then we will be able to use this information and provide you a query which we tested on your sample.

    The basic format of the query should be (assuming both queries above are on the same table):

    UPDATE myTable SET myField = REPLACE(myField, 'aaa', 'bbb')
    WERE myField LIKE '%a'


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Monday, September 9, 2019 4:06 PM
    Moderator
  • Hi wakefun,

     

    Thank you for your issue.

     

    You can try to use CTE to UPDATE your data or use UPDATE directly , and I think Naveen's script have solved your issue.

     

    In addition, I am not sure why you would like to use cursor . As I know ,  generally cursor should be avoided because they bring a negative impact on performance, especially when operating on a large sets of data. In most cases, we can use JOINS, even WHILE clauses, SSIS packages or other alternative methods to get the same result quicker, with less impact on performance output and even writing fewer lines of syntax.

     

    So please try the first reply. If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 10, 2019 6:36 AM
  • Thanks for all answer,

    The problem I have is that I have more than 110000 records to be updated and I would like to update the records in bulk update.

    I have see some sample on this but was has below :

    WHILE 1 = 1
    BEGIN
    
    BEGIN TRANSACTION
    
       UPDATE TOP (5000) myTable SET myField = REPLACE(myfield, 'aa;', 'bb')
       FROM mytable 
       IF @@ROWCOUNT < 5000 BREAK;
    
    END

    What is wierd in this sample is that there is a WHILE 1=1 to make infinite loop and I have been told it is dangerous to use it like this. what will be the alternative ?

    Thnaks for help

    regards

    Tuesday, September 10, 2019 6:59 AM
  • Hi

    You can directly use Batching query to update the records as mentioned in first reply and that's the alternative way to usage of Looping concepts which should be avoided until necessary in batching activities.(Dealing with Large data sets)

    Hope this is helpful

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, September 10, 2019 7:10 AM
  • Dear Naveen Kumar,

    How to update you first reply to use batch ? 

    sorry for basic thing never done such approach

    regards

    Tuesday, September 10, 2019 7:19 AM
  • An alternative to ‘WHILE 1=1’:

       declare @n as int = 1

       while @n <> 0

       begin

          update . . .

          set @n = @@ROWCOUNT

       end

    Tuesday, September 10, 2019 7:20 AM
  • Hi 

    I am just referring to my first reply.

    Sample code looks as below

    DECLARE @Count int ,@batch int;
    SET @batch = 0;
    SELECT @Count = COUNT(1)
    FROM mytable
    WHERE myField LIKE '%a';
     
    WITH Cte as (
    SELECT ID,myField,ROW_NUMBER() OVER(ORDER BY ID) AS Rno
    FROM mytable
    WHERE myField LIKE '%a')
    
    
    WHILE (@batch < @Count)
    BEGIN
    	UPDATE Cte 
    	SET myField = REPLACE(myField, 'aaa', 'bbb')
    	WHERE Rno < 5000+@batch;
    
    	SET @batch = 5000+@batch
    END

    Thank you


    If this post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.


    Tuesday, September 10, 2019 7:26 AM