ERROR 1364 (HY000): Field 'Name' doesn't have a default value

user_15140888 0
Viewed 466

I am trying to split a column into three different columns and insert the split values into the table.

This is the statement I am using.

INSERT INTO table1 (X, Y, Z)
SELECT 
     SUBSTRING_INDEX(XYZ, "-", 1),
     SUBSTRING_INDEX(SUBSTRING_INDEX(XYZ, "-", -2), "-", 1),
     SUBSTRING_INDEX(SUBSTRING_INDEX(XYZ, "-", -1), "-", 2)
FROM table1;

However I get this error:

ERROR 1364 (HY000): Field 'Name' doesn't have a default value

This doesn't make much sense to me because I did not call the "Name" field at all.

I know for certain that on their own the select statements work, but the insert into does not seem too.

If anyone has any advice or suggestions it would be much appreciated!

mysql sql
1 Answers

Apparently, you have a column called name which is declared NOT NULL and has no DEFAULT value. INSERT inserts new rows and usually you want values for some or all of the columns.

I suspect that what you really want is not INSERT, but UPDATE -- to change values within a row:

UPDATE table1
    SET X = SUBSTRING_INDEX(XYZ, '-', 1),
        Y = SUBSTRING_INDEX(SUBSTRING_INDEX(XYZ, '-', -2), '-', 1),
        Z = SUBSTRING_INDEX(SUBSTRING_INDEX(XYZ, '-', -1), '-', 2);
user_11440350
Related