First create a table with the "fixed" domain names:
CREATE TABLE FixedDomains
SELECT DISTINCT domain, left( domain, instr( domain, '.'> ) -1 ) as
newdomain
FROM url_cat
Index your new table (for speed):
ALTER FixedDomains ADD INDEX (Domain)
*** NOTE: You really want to review (and modify, if necessary) the data in
FixedDomains _before_ you do the next step. Make sure that the newdomain
column has the correct extract for the value in the domain column. ***
Now you can mass-update your data:
UPDATE url_cat u
INNER JOIN FixedDomains fd
ON fd.domain = u.domain
SET u.domain = fd.newdomain
You can then "DROP TABLE FixedDomains" whenever you are through using it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Martin Rytz" <[EMAIL PROTECTED]> wrote on 08/10/2004 09:51:41 AM:
> Hi all
>
> I have a problem with slow update queries like these (5 examples):
>
> update url_cat set domain = '01net' where left( domain, instr( domain,
'.' )
> -1 ) = '01net';
> update url_cat set domain = '1-meta' where left( domain, instr( domain,
'.'
> ) -1 ) = '1-meta';
> update url_cat set domain = '105' where left( domain, instr( domain, '.'
)
> -1 ) = '105';
> update url_cat set domain = '123love' where left( domain, instr( domain,
'.'
> ) -1 ) = '123love';
> update url_cat set domain = 'google' where left( domain, instr( domain,
'.'
> ) -1 ) = 'google';
>
> before the update, the field domain contains 'google.com' or 'google.de'
or
> 'google.ch' and after the update it contains only 'google'.
>
> i have to make thousends of this updates an it takes a long time. the
table
> 'url_cat' contains about 100'000 entries! an index would help, but mysql
> does not use any static index. the index should contain the
> where-condition... but this is not possible, because the lenght of the
> condition differs?!
>
> does anybody have another idea?
>
> thank you in advance.
> martin
