Search code examples
mysqlsqlinsertmysql-error-1292

MySQL Error 1292 on insert text

I am trying to insert a combination of 2 fields into one field and every time I do this I get an error about truncating data.

The code:

insert into sharppatentdb.Inventor (InventorFull)
select InventorFirst + " " + InventorLast 
from sharppatentdb.inventorsource;

The error:

Error Code: 1292. Truncated incorrect DOUBLE value: 'Rossi-Wolter'

All source fields are VARCHAR(64) The InventorFull field is VARCHAR(511) What am I doing wrong?

Solution

  • So the problem is that InventorFull needs to be 511 characters based on this statement:

    All fields are VARCHAR(255)

    because somewhere in the mix the concatenation of the two fields InventorFirst, the single space, and InventorLast is greater than 255 characters. When you build fields for concatenated values they need to be the combined size of both fields as well as any additional characters.

    To more clearly depict the reason that your InventorFull field needs to be 511 characters - your two fields are 255 characters and you're adding a single character in between:

    255 + 255 + 1 = 511

    further, as shown in the comments, it's worth noting that this will not work on MySQL versions that are less than 5.0.3.