Merging two fields into one

[sgtpepper]

Hi,

I have a database with fields called lass_town and lass_state. I am merging data from this table into another table....using the following mysql query.

mysql_query("INSERT INTO realtymls.mastertable (Id,town,list_price,type)
SELECT lass_id,lass_city,lass_price1,type FROM homesfsbo.lass");

What I would like to be able to do is something like the following:

mysql_query("INSERT INTO realtymls.mastertable (Id,town,list_price,type)
SELECT lass_id,lass_city lass_state,lass_price1,type FROM homesfsbo.lass");

I would like the result for the state to be added to the merged town field.

The results for lass_city is Claremont and the result for lass_state is NH.....so I would like the merged result to be Claremont NH

Any ideas on how I need to restruture my query?

Thanks.

[Huuggee]

I was going to say, you can pull back the result set that you want to insert and pop it into an array and concatonate the two fields you want back into the insert query.

However I know from mssql that mysql must allow you to concatonate on the query, and it does!!

mysql_query("INSERT INTO realtymls.mastertable (Id, town, list_price, type)
SELECT lass_id, CONCAT(lass_city, ' ', lass_state), lass_price1, type FROM homesfsbo.lass");

it seems the CONCAT function will merge it all into the single cell result for you.

downside is i don't think it's that efficient

[Cailean]

Originally posted by Huuggee downside is i don't think it's that efficient

It's gotta more effecient than pulling the data from homesfbso.lass concatenating the fields with php and then inserting it all into realtymls.mastertable.