Set Update - SQL Server Forums

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)

Set Update

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 13:40:26

OK...so Why doesn't the multiple updates get applied to the last row?

[CODE]
USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO
INSERT INTO myTable99(Col1)
SELECT '1~2~3~4~5' UNION ALL
SELECT '1~2~3~4~5' UNION ALL
SELECT '1,2~3,4,5'
GO

CREATE TABLE myTable00(Old varchar(10),New varchar(10))
GO

INSERT INTO myTable00(Old,New)
SELECT '~', '' UNION ALL
SELECT ',',''
GO

SELECT * FROM myTable99

UPDATE a SET Col1 = REPLACE(Col1,Old,New)
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 LIKE '%'+Old+'%'

SELECT * FROM myTable99
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

[/CODE]

any ideas?

Is it like SELECT @x = col from table

Where there is more than 1 row in the rs

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-03-19 : 14:21:19

Exact same results with PATINDEX.

UPDATE a SET Col1 = REPLACE(Col1,Old,New)
FROM myTable99 a
INNER JOIN myTable00 b
ON PATINDEX('%'+old+'%', a.Col1) > 0

Odd!

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-03-19 : 14:37:32

So I'm stuck having to write a cursor?

Drag

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-19 : 14:40:36

Vedy intedesting !

The last row is represented twice in the recordset 'a'. The update result of the 2nd occurance of the last row is saved. The first is gone gone gone. Golly !

I'm sure this is clearly explained in BOL somewhere. Where are the BOL gurus?

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-19 : 14:48:12

Brett, this forum forgives cursing, but not cursors.

This is a Rube Goldberg, but you could repeatedly execute the update until all rows were revised.

Ha ! It works !
[CODE]
USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8000))
GO
INSERT INTO myTable99(Col1)
SELECT '1~2~3~4~5' UNION ALL
SELECT '1~2~3~4~5' UNION ALL
SELECT '1,2~3,4,5'
GO

CREATE TABLE myTable00(Old varchar(10),New varchar(10))
GO

INSERT INTO myTable00(Old,New)
SELECT '~', '' UNION ALL
SELECT ',',''
GO

SELECT * FROM myTable99

UPDATE a SET Col1 = REPLACE(Col1,Old,New)
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 LIKE '%'+Old+'%'

DECLARE @rowc INT
SET @rowc = 1

WHILE @rowc > 0 BEGIN

UPDATE a SET Col1 = REPLACE(Col1,Old,New)
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 LIKE '%'+Old+'%'

SET @rowc = @@ROWCOUNT

END

SELECT * FROM myTable99
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources