Insert concatenated values of two colums in a table into a single column of another table

mysql sql concatenation mysql-error-1292
Kumaran Senapathy picture Kumaran Senapathy · Feb 28, 2013 · Viewed 7.6k times · Source

I have a table that has three columns. They are of type VarChar. I am looking to concatenate the values on first and second column and insert that into the First column of another table.

I used this code

insert into table2(cloumn1)
select city+''+Coalesce(zipcode) from table1

I get an error 

Error Code: 1292. Truncated incorrect DOUBLE value: 'london'

"London" is the value of the first row and the second row has values like "123.2.4.4" Both columns are declared as VarChar.

What should I change in the query to get values in the table2 that look like "london 123.2.4.4" ??

Answer

Taryn picture Taryn · Feb 28, 2013

You should use the CONCAT() function to concatenate the strings:

insert into table2(cloumn1)
select CONCAT(city, Coalesce(zipcode, '')) 
from table1

And be sure that the datatype of the column you are inserting into is a varchar. If the datatype is a double, then you will receive this error.

Related questions

SQL UPDATE all values in a field with appended string CONCAT not working

Here is what I want to do: current table: +----+-------------+ | id | data | +----+-------------+ | 1 | max | | 2 | linda | | 3 | sam | | 4 | henry | +----+-------------+ Mystery Query ( something like "UPDATE table SET data = CONCAT(data, 'a')" ) resulting table: +----+-------------+ | id | data | +----+-------------+ | 1 | …

Read More
How to use GROUP BY to concatenate strings in MySQL?

Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C

Read More
MySQL Results as comma separated list

I need to run a query like: SELECT p.id, p.name, (SELECT name FROM sites s WHERE s.id = p.site_id) AS site_list FROM publications p But I'd like the sub-select to return a comma separated list, …

Read More