current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 9 down vote favorite
1

I have a table and I need to update some names but I was wondering about the

following queries :

Will both do the same?

Query1

Update mytable
Set Name = Replace(Name,'Jeff','Joe')

Query2

Update mytable
Set Name = 'Joe'
where Name = 'Jeff'
share|improve this question
up vote 21 down vote

No, they will not do the same thing.

  1. The amount of effort required by the SQL engine is completely different. In the first query, the engine must go through every row and perform a string replace operation on the Name column. In the second query, it is searching through the table where the name is "Jeff" and just updating the Name column to be Joe.

  2. String replacement is wildcard. So in the first query, the Name "Jeffrey" would become "Joefrey".

share|improve this answer
    
It would become, Joerey, no? – jpmc26 1 hour ago
up vote 5 down vote

No, it's not the same.

The first query looks for string within the entire string (see replace() "Replaces all occurrences of a specified string value with another string value."), the second looks for the exact string.

If you have records like JeffJoe, the first query will give JoeJoe, the second query will give JeffJoe (without any modification).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.