1

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?

Johnny B
  • 420
  • 1
  • 5
  • 14

1 Answers1

0

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.

Mike Perrenoud
  • 66,820
  • 29
  • 157
  • 232
  • @FreshPrinceOfSO, I'm guessing you're looking for `255 + 255 + 1`? – Mike Perrenoud Apr 01 '13 at 19:51
  • Very good. I assume this works in MySQL versions `<` MySQL 5.0.3? – Kermit Apr 01 '13 at 19:53
  • @FreshPrinceOfSO, are you saying that versions less than 5.0.3 wouldn't support a field of `511` characters? Maybe I misunderstood the question. – Mike Perrenoud Apr 01 '13 at 19:54
  • That's correct. [Reference](http://dev.mysql.com/doc/refman/5.0/en/char.html) (I just think it's worth noting in your answer) – Kermit Apr 01 '13 at 19:55
  • ahhhhhhhhhhhhhhhhhhhhhhhh that makes sense, I thought that it just allowed up to 255 chars. I didnt realize it filled with space. I will just make my source fields 127 chars then. Thanks! – Johnny B Apr 02 '13 at 13:31