Can I do this without a cursor?

Consider a table called Target with a character column called StringColumn.
I
also have a lookup table called ToReplace which contains 2 columns, OldValue
and NewValue. I'm trying to use the replace function to update the
StringColumn value in the Target table, finding the text in OldValue and
replacing it with the text in NewValue from the ToReplace table. The update
I'm using just grabs the first record from the ToReplace table. Can anyone
think of a way to do this in one update statement? Thanks in advance. Here i
s
some sloppy ddl, but it gives you the idea:
Create table ToReplace
(OldValue varchar(10),
NewValue varchar(10))
insert into ToReplace
values
('old1', 'new1')
insert into ToReplace
values
('old2', 'new2')
insert into ToReplace
values
('old3', 'new3')
Create table Target
(StringColumn varchar(50))
insert into target
values
('old1 some other text old3')
insert into target
values
('old3 old2 some other text')
select * from target
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
from ToReplace
select * from targetI'm . Why are you just using a simple update
statement against the table instead of all this insert
and create table stuff?
Update target
set StringColumn = replace(StringColumn, OldValue, NewValue)
where some condition is true
Robbe Morris - 2004-2006 Microsoft MVP C#
Earn money answering .NET questions
http://www.eggheadcafe.com/forums/merit.asp
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
> Consider a table called Target with a character column called
> StringColumn. I
> also have a lookup table called ToReplace which contains 2 columns,
> OldValue
> and NewValue. I'm trying to use the replace function to update the
> StringColumn value in the Target table, finding the text in OldValue and
> replacing it with the text in NewValue from the ToReplace table. The
> update
> I'm using just grabs the first record from the ToReplace table. Can anyone
> think of a way to do this in one update statement? Thanks in advance. Here
> is
> some sloppy ddl, but it gives you the idea:
> Create table ToReplace
> (OldValue varchar(10),
> NewValue varchar(10))
> insert into ToReplace
> values
> ('old1', 'new1')
> insert into ToReplace
> values
> ('old2', 'new2')
> insert into ToReplace
> values
> ('old3', 'new3')
>
> Create table Target
> (StringColumn varchar(50))
> insert into target
> values
> ('old1 some other text old3')
> insert into target
> values
> ('old3 old2 some other text')
>
> select * from target
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> from ToReplace
> select * from target
>|||Now I'm . That insert and create table stuff is the ddl. If you run
it all you'll see my problem.
"Robbe Morris [C# MVP]" wrote:

> I'm . Why are you just using a simple update
> statement against the table instead of all this insert
> and create table stuff?
> Update target
> set StringColumn = replace(StringColumn, OldValue, NewValue)
> where some condition is true
>
> --
> Robbe Morris - 2004-2006 Microsoft MVP C#
> Earn money answering .NET questions
> http://www.eggheadcafe.com/forums/merit.asp
>
>
> "sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
> news:89BB53E2-C2BA-4BB5-9533-A940FFC3B403@.microsoft.com...
>
>|||The problem you have run into relates to why UPDATE has no FROM clause
in standard SQL.
A row in the table you are updating matches more than one row in the
table you are joining it to. However, the row you are updating is
always the same "before image" of that row. So one matching ToReplace
changes old1, another changes old2, but they BOTH change the ORIGINAL
image of the row in Target. So, the row in Target gets updated more
than once, but the result is any (unpredictable) ONE of the updates,
not all of them together.
I would simply run each value to be changed as a single UPDATE, with
hard-coded values, rather than use any sort of ToReplace table.
Otherwise you need to work in some sort of loop to apply only one
ToReplace row at a time.
Also, it would have been a good idea to add a WHERE clause to your
UPDATE:
where StringColumn like '%' + OldValue + '%'
Roy Harvey
Beacon Falls, CT
On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
<sqlboy2000@.discussions.microsoft.com> wrote:

>Consider a table called Target with a character column called StringColumn.
I
>also have a lookup table called ToReplace which contains 2 columns, OldValu
e
>and NewValue. I'm trying to use the replace function to update the
>StringColumn value in the Target table, finding the text in OldValue and
>replacing it with the text in NewValue from the ToReplace table. The update
>I'm using just grabs the first record from the ToReplace table. Can anyone
>think of a way to do this in one update statement? Thanks in advance. Here
is
>some sloppy ddl, but it gives you the idea:
>Create table ToReplace
>(OldValue varchar(10),
>NewValue varchar(10))
>insert into ToReplace
>values
>('old1', 'new1')
>insert into ToReplace
>values
>('old2', 'new2')
>insert into ToReplace
>values
>('old3', 'new3')
>
>Create table Target
>(StringColumn varchar(50))
>insert into target
>values
>('old1 some other text old3')
>insert into target
>values
>('old3 old2 some other text')
>
>select * from target
>Update target
>set StringColumn = replace(StringColumn, OldValue, NewValue)
>from ToReplace
>select * from target
>|||Thanks Robbe, I didn't see any elegant solution to this either. I'll just
loop through it, it's a nightly run, so it's not the end of the world.
"sqlboy2000" wrote:
> Now I'm . That insert and create table stuff is the ddl. If you ru
n
> it all you'll see my problem.
> "Robbe Morris [C# MVP]" wrote:
>|||Sorry, got your name wrong. Thank you Roy
"Roy Harvey" wrote:

> The problem you have run into relates to why UPDATE has no FROM clause
> in standard SQL.
> A row in the table you are updating matches more than one row in the
> table you are joining it to. However, the row you are updating is
> always the same "before image" of that row. So one matching ToReplace
> changes old1, another changes old2, but they BOTH change the ORIGINAL
> image of the row in Target. So, the row in Target gets updated more
> than once, but the result is any (unpredictable) ONE of the updates,
> not all of them together.
> I would simply run each value to be changed as a single UPDATE, with
> hard-coded values, rather than use any sort of ToReplace table.
> Otherwise you need to work in some sort of loop to apply only one
> ToReplace row at a time.
> Also, it would have been a good idea to add a WHERE clause to your
> UPDATE:
> where StringColumn like '%' + OldValue + '%'
> Roy Harvey
> Beacon Falls, CT
> On Thu, 9 Mar 2006 13:39:27 -0800, sqlboy2000
> <sqlboy2000@.discussions.microsoft.com> wrote:
>
>