Multiple Update Without a Cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple Update Without a Cursor

Author

Topic

d473566
Starting Member

23 Posts

Posted - 2003-08-26 : 15:15:18

Hey
Here is the problem I was given:

Find all documents with 2 spaces in the name and replace with 1 space.

I am pretty sure I could do this in 2 secs using a cursor, but would like to get away from that practice since it is such a poor performer and it is generally frowned upon by the gurus of these forums

I have gotten the code to the point where:
1) I find all document name with a double space
2) replace the double space with a single space

--------------------------------------------------------
DECLARE @temp TABLE(ID INT, shortname VARCHAR(500),shortnameNew VARCHAR(500))
INSERT INTO @temp
SELECT ID,
shortname,
shortnameNew = ''
FROM dbo.document
WHERE CHARINDEX (' ' , shortname,0) <> 0

UPDATE @temp
SET shortnameNew = REPLACE ( shortname , ' ' , ' ' )

--------------------------------------------------------

Now, how do I get the new name in the temp table into the real table, without using a cursor. How do I loop through and do the updates?

Am I even on the right track here??

Thanks in advance.

HeatherWitt
Starting Member

8 Posts

Posted - 2003-08-26 : 17:40:13

Why not run the update directly agaist the table that stores the document names? Unless you want to store the old table name for some reason, you should be able to run the update directly. Basically, it would look the same as the statement you ran against the temporary table, except that you would replace the shortnamenew field name with shortname:

UPDATE dbo.document
SET shortname = REPLACE(shortname, ' ', ' ' )

- Heather

nr
SQLTeam MVY

12543 Posts

Posted - 2003-08-26 : 19:17:05

ANd if you wanted to use a table variable you would populate it with

INSERT INTO @temp
SELECT ID,
shortname,
shortnameNew = REPLACE ( shortname , ' ' , ' ' )
FROM dbo.document
WHERE CHARINDEX (' ' , shortname,0) <> 0

What if there are 3 spaces?
You might want to run repeated updates on the table until no rows are affected.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

d473566
Starting Member

23 Posts

Posted - 2003-08-26 : 23:46:12

Geez...talk about not seeing the trees through the forest. I guess I was just trying too hard!
That solution is too easy...thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources