Tricky MySQL GROUP_CONCAT

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cyberwill
Forum Newbie
Posts: 1
Joined: Wed Jun 17, 2009 2:50 pm

Tricky MySQL GROUP_CONCAT

Post by cyberwill »

Hey I've sort of new at MySQL so I'm hoping some of you guys can help me out. I'll simplify my example as much as possible.

I have 2 tables and I'm trying to do an update join. The first table has a list of cities and states, and a blank column for zip codes that I'm trying to fill in.

I want to do an update and grab only 3 zip codes for each city/state and put them into client_table.origin_zip_code as a comma delimited list.

Here's what I've got and it almost works. Any help would be fantastic....

UPDATE client_table A SET
A.origin_zip_code =
(
SELECT GROUP_CONCAT(T.zip_code SEPARATOR ',')

FROM (

SELECT DISTINCT zip_code
FROM zip_codes_table B

WHERE
lcase(B.city) = lcase(A.origin_city)
AND lcase(B.state_code) = lcase(A.origin_state)
LIMIT 3
) AS T
)

Thanks so much for your help...
Post Reply