[SOLVED] Need query help

[wood6994]

I have a table “zip_codes” that has “zip_code”, “city” & “state_name” fields

I also have the “profile” table that has “city” & “state” fields which most users have populated

I added an empty field “zip” to the “profile” table, and I want to populate it with the results from a query that looks up the zip code from the “zip_codes” table as it relates to the “state_name” and “city” in the same table.

 

Can anyone help?

 

Here’s what I was trying…

 

INSERT INTO profile

(zip)

SELECT zip_code

FROM zip_codes

WHERE city IN(SELECT pr.city FROM profile AS pr

INNER JOIN zip_codes AS zc ON pr.city=zc.city

WHERE pr.city=zc.city AND pr.location=zc.state_name)

[fenway]

Don't you mean UPDATE?

[wood6994]

Don't you mean UPDATE?

 

Yes, you're right.  I actually got it figured out.  Here's what I used.

 

UPDATE profile pr

INNER JOIN zip_codes zc

  ON pr.state = zc.state_prefix

  AND pr.city = zc.city

SET pr.zip = zc.zip_code