INSERT SELECT with string SQL

Question

i want to copy the content of one column to another column. The copied content should be linked to a string.

|  company  |  new_company  |
_____________________________
Google      | The Google Company

Here the "The" and "Company" are attached as a string to the content.

I have tried a lot, which is the most meaningful to me looks like:

INSERT INTO table_name (copy_into_column)
SELECT CONCAT('The ', copy_from_column, ' Company')
FROM table_name

But with this sql i get that error:

#1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 'time' at row 1035

Of course, I have several columns in my right table, but actually should be disregarded or?

I hope someone has an idea.

Possible duplicate of [Error in mysql when setting default value for DATE or DATETIME](https://stackoverflow.com/questions/36374335/error-in-mysql-when-setting-default-value-for-date-or-datetime) — krokodilko, Aug 15 '17 at 09:38
time Null = No, but each entry in the column time has a value — Dieter Information, Aug 15 '17 at 09:39
maybe you have to reformat your datetime from string to date with `STR_TO_DATE('18,05,2009','%d,%m,%Y'); ` ? — flowers1234, Aug 15 '17 at 09:43
i cant change the type of fields or even the date. it is a database with tousend of entrys. and it is already a finish script behind it. is there a way to ignore these columns? — Dieter Information, Aug 15 '17 at 10:39

score 0 · Answer 1 · edited Aug 15 '17 at 10:48

0

Looks like there is another column named time that does not accept null values. You can try inserting current time instead, e.g.:

INSERT INTO table_name (copy_into_column, time)
    SELECT CONCAT('The ', copy_from_column, ' Company'), NOW()
    FROM table_name;

edited Aug 15 '17 at 10:48

Gordon Linoff

1,242,037
58
646
786

answered Aug 15 '17 at 10:00

Darshan Mehta

30,102
11
68
102

score 0 · Accepted Answer · answered Aug 15 '17 at 10:54

Aren't you looking for update statement?

UPDATE table_name
SET copycol = CONCAT('The ', copy_from_column, ' Company')

About the error that you get:

You have a timestamp field that apparently is required. from the code you've shared, I see that you are inserting new rows into DB, where I guess you don't fill the other columns.

INSERT SELECT with string SQL

2 Answers2

About the error that you get: