13

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'
Improve this question
11

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).

Improve this answer
26

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 "Joerey".

Improve this answer
2
  • 3
    Point 2 seems the more important one. If it doesn't do the right thing performance is irrelevant.
    – Martin Smith
    Feb 11 '17 at 9:26
  • 1
    You are probably correct, but we actually don't know what the business objective of the update is supposed to be. I should have reversed the order of the analysis, but I think mentioning both flaws was the correct thing to do. One thought that comes to mind is that the OP simplified it for us, but left out details, such that "Name" actually contains the full name rather than just the first name.
    – Jonathan Fite
    Feb 13 '17 at 14:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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