alternative to sql cursor

Question

I am currently using a sql cursor to look up a table to update another table. I have a table which contains a lot of phrases. I want update another table to set 1 if any of those phrases falls into any of the column in the update table. I am using cursor and char to look for the phrase. The cursor is taking long time and I'm just wondering if I could use anything else instead of the cursor. Thanks. I'm using sql server and here's the code

declare @word varchar(max)
    declare @aCursor cursor for
SELECT col from table
    open acursor
    fetch next from acursor into @word
    while @@fetch_status=0
    begin
SET @word = '' + @word + ''
UPDATE updatetable
SET updatecol = 'y'
FROM updatetable u, tableb b
WHERE u.id = b.id AND (CHARINDEX(@word, u.name) > 0 OR CHARINDEX(@word, u.city) >
    fetch next from acursor into @word
    end
    close acursor
    deallocate acursor

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are **highly** vendor-specific - so we really need to know what **database system** (and which version) you're using.... — marc_s, Feb 18 '13 at 19:31
Cross applies are also an option: http://stackoverflow.com/questions/7492797/using-cross-apply-in-update-statement — Limey, Feb 18 '13 at 19:41

score 5 · Answer 1 · answered Feb 18 '13 at 19:27

Take a look at: http://weblogs.sqlteam.com/jeffs/archive/2008/06/05/sql-server-cursor-removal.aspx that should get you way on your way. I, however, have come full cricle on the issue, for individual row manipulation, cursors ARE the way to go, performance is about the same as other methods and readability is 10x better than the other methods making maintaining the code a lot easier.

However, I don't have enough detail it seems to understand why you can't solve this with an update statement.

alternative to sql cursor

1 Answers1