On Jul 2, 4:12=A0pm, "Roy Harvey (SQL Server MVP)" <roy_har...@[EMAIL PROTECTED]
>
wrote:
> First of all get rid of the TOP (100) PERCENT nonsense. =A0While it
> should have no effect, it serves no purpose and just confuses things.
>
> Second, if you are saying that not all rows you expect to be updated
> are updated, turn your UPDATE commands into queries and see what is
> returned. =A0If the SELECT returns rows using a given WHERE clause, then
> an UPDATE with the same WHERE clause should update the same rows. Also
> double check the spelling of the literals; a different spelling of
> 'Boulevard' in the WHERE clause and SET clause would not work right.
>
> If you want to do this in a single query you need to nest the REPLACE
> functions, and OR the tests.
>
> UPDATE dbo.All_Client_Companies_For_Fix
> SET address_line_1_fix =3D
> =A0 =A0 =A0 =A0REPLACE(
> =A0 =A0 =A0 =A0REPLACE(
> =A0 =A0 =A0 =A0REPLACE(
> =A0 =A0 =A0 =A0REPLACE(address_line_1,
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'Boulevard','Blvd'),
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'Street','St'),
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'Avenue','Ave'),
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0'Road','Rd')
> WHERE (address_line_1 like '%Road%'
> OR =A0 =A0 address_line_1 like '%Avenue%'
> OR =A0 =A0 address_line_1 like '%Street%'
> OR =A0 =A0 address_line_1 like '%Boulevard%')
>
> Roy Harvey
> Beacon Falls, CT
I started without the TOP clause but since didn't update, I tried it
as an option (no problem removing). When I execute the query, I get
re****ting to the effect that there were updates applied. See below.
Which leads me to the solution that I just figured out while typing
this.... I'm replacing the subsequent updates from the original
Address (and undoing the previous statements).
(3597 row(s) affected)
(2970 row(s) affected)
=2E....
(95 row(s) affected)
(142 row(s) affected)
The fix was to move address_line_1_fix (not - address_address_line_1)
into the replace clause:
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix =3D REPLACE(address_line_1_fix,'Street','St')
WHERE address_line_1_fix like '%Street%'
UPDATE dbo.All_Client_Companies_For_Fix
SET address_line_1_fix =3D
REPLACE(address_line_1_fix,'Boulevard','Blvd')
WHERE address_line_1_fix like '%Boulevard%'
GO
|
