Tricky MYSQL UPDATE, SELECT, WHERE

[emajane]

UPDATE
ilo_user u
INNER JOIN  
ilo_zip_codes z 
ON 
u.zipcode = zip 
SET 
u.zipcode_id = z.id

 

No Errors, just duplicates... many... and it takes forever to execute just a few.

We have two tables.

 

Table A (ilo_user) USERS table

- has field 'zipcode'

- has field 'zipcode_id'

 

Table B (ilo_zip_codes) ZIP CODES table

- has field 'id'

- has field 'zip'

 

We are attempting to update our USERS table, updating the 'zipcode_id' field on the USERS table with the 'id' field from the ZIPCODES table by matching the USERS 'zipcode' field with the ZIPCODES 'zip' field.

 

TABLE STRUCTURE: ilz_zip_codes

id int(11) NO PRI auto_increment

parentId int(11) NO

zip varchar(10) YES MUL

 

TABLE STRUCTURE: ilz_user

id int(11) NO PRI auto_increment

zipcode varchar(20) YES

zipcode_id int(11) YES

 

We also tried:

UPDATE
ilo_user u, ilo_zip_codes z
SET
u.b_zipcode_id = z.id
WHERE
u.zipcode = zip 

 

 

Any help is greatly appreciated. We are new to this forum and glad we found it.

 

My Server Info:

 

    Server: Localhost via UNIX socket

    Server version: 5.1.63-cll

    Protocol version: 10

    User: root@localhost

    MySQL charset: UTF-8 Unicode (utf8)

 

    cpsrvd 11.32.4.14

    MySQL client version: 4.1.22

    PHP extension: mysql Documentation

 

[Jessica]

What do you mean by duplicates, it's an update so there's no way for it to create any duplicate data.

 

Do you have indexes on the columns?

[emajane]

I apologize. I didn't word that right. The following code gives us many duplicate results.

SELECT
u.business_name, u.zipcode, z.id, z.zip
FROM
ilo_user u, ilo_zip_codes z
WHERE	
u.zipcode = zip 

We were just trying to see what results we were getting, and why the updates were taking so long.

no indexes on tables.

 

Does our update code look right?

 

[Jessica]

The update looks fine to me.

Your select however will produce duplicate results because you are joining every row in users to every row in zip codes. You haven't told it how to join. When you join you need to specify ON what column, like you did in your update. You can also make it an inner if every row in users has a matching row in zip codes. Inner is faster than left.

 

And add indexes!!

[emajane]

Thanks! So on an update like this, let's say there are 100 rows of data, shouldn't that be pretty quick? We have a monster server with 32 GB ram... not the server, is such a statement taxing and complex to take a few minutes to go through 100 rows of users.

I guess however our zipcode table has over 86,000 records.

[Jessica]

I can't speak as to why the update takes so long, but if you add indexes it should help.

 

This is a one time thing, right?

[emajane]

Correct, one time. But we will use on multiple tables as we are merging data from one site to another.

[fenway]

That doesn't really matter -- take the UPDATE, write the equivalent SELECT,and check EXPLAIN.

[smoseley]

ALTER TABLE ilo_user ADD INDEX (zipcode);

ALTER TABLE ilo_zip_codes ADD INDEX (zip);

 

That'll make it go faster.