Wednesday, March 28, 2012

Problems in SQL, Updating many rows at once

Howdy all.
I have a person table. The relavent columns are:
PersonID INT
LastName VARCHAR(30)
LastNameSndx CHAR(4)
I'm almost embarressed to ask considering my SQL expertise, but...
I need to take the SOUNDEX of the LastName and put that value in the LastNam
eSndx column. So I fire off this SQL:
Update Person Set LastNameSndx = SOUNDEX(LastName);
It takes forever. I start tweeking the SQL to commit every 500 records or so
. Still takes a long time. I then notice what is happening. The SQL is takin
g the SOUNDEX of the LastName of the first record, apply it to ALL the recor
ds, then taking the SOUNDEX
of the LastName of the second records, then updating it to ALL the records,
etc.
This is not SQL as I understand it.
What am I doing wrong here?Hi!
quote:

> It takes forever. I start tweeking the SQL to commit every 500 records or

so. Still takes a long time. I then notice what is happening. The SQL is
taking the SOUNDEX of the LastName of the first record, apply it to ALL the
records, then taking the SOUNDEX of the LastName of the second records, then
updating it to ALL the records, etc.
quote:

>

How did you notice this? This is really strange, I've never heard of
something like this.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.|||I attempted the same thing and I am not seeing the same behavior. Not sure
what is happening in your case.
Rand
This posting is provided "as is" with no warranties and confers no rights.

No comments:

Post a Comment