First time running REPLACE - 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

General SQL Server Forums

New to SQL Server Programming

First time running REPLACE

Author

Topic

BIGGY
Starting Member

17 Posts

Posted - 2006-03-31 : 14:05:49

Wanted to verify how I would run a replace...it's actually my first time doing a replace, but figure it's worse to screw this up than just a simple SELECT :)

The table/field is:
SOHeader.ShiptoID
I need to go through and replace only values which are equal to '9999' with the word 'DEFAULT' instead for all rows in that column.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-31 : 14:15:43

First do a backup of the table as follows:
Select * into SOHeaderBkup from SOHeader

Check the BOL for Syntax & use of Replace

Then
Update SOHeader set ShiptoID = Replace(ShiptoID,'9999','DEFAULT')

Srinika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-31 : 16:04:55

i usually just do stuff like this:
and everybody should do it like that if you ask me


begin tran
-- update
Update SOHeader set ShiptoID = Replace(ShiptoID,'9999','DEFAULT')

-- check if replace worked properly
select ShiptoID from SOHeader
rollback
-- when satisfied comment the rollback and uncomment the commit
-- commit

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

BIGGY
Starting Member

17 Posts

Posted - 2006-03-31 : 16:12:55

Thanks, that should work perfectly. Now I was told that instead of doing it across the board that it's now for certain customers. So my guess is that's taken care of as usual through a WHERE statement and would look something like:

Update SOHeader set ShiptoID = Replace(ShiptoID,'9999','DEFAULT')

WHERE
soheader.custid = 'Customer1' AND
soheader.custid = 'Customer2'
...etc?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-31 : 16:14:09

yes

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

BIGGY
Starting Member

17 Posts

Posted - 2006-04-03 : 16:36:03

One more...can something be done like...

Update SOHeader set ShiptoID = Replace(ShiptoID,*,SOAddress.AddressID)

WHERE
soheader.custid = 'Customer1'

So if I want to replace all values of shiptoid with those from soaddress.addressid?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-03 : 16:42:09

Biggy, U may need to have a good look at the Update Statement in BOL or some other source.

U may not use "All" as * Always.

Srinika

BIGGY
Starting Member

17 Posts

Posted - 2006-04-04 : 10:33:43

Sorry, what's BOL?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-04-04 : 10:35:04

BOL = Books Online = SQL server help

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="http://weblogs.sqlteam.com/mladenp"]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-04 : 14:35:57

Hi all,

Am I missing something? Is the replace function right here? Surely this is just an update...

Update SOHeader set ShiptoID = 'DEFAULT' where ShiptoID = '9999'

I'm not sure we'd want to change '19999' to '1DEFAULT'!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources