Hello all,
I'm having a problem with an update using this query:
It fails at the update. I get a 1366 error (Incorrect integer value: '' for column 'thumb_seq' at row 1). The 'level_08' column data is a string and I'm pulling a numeric value from that string and setting it to the 'thumb_seq' column, where it needs to be a number (and sortable as a number). Where am I going wrong?
I'm having a problem with an update using this query:
Code:
DROP TABLE IF EXISTS test_table;
CREATE TABLE test_table
(
pk_newline_id INT(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (pk_newline_id),
level_01 VARCHAR(255) default '0',
level_02 VARCHAR(255) default '0',
level_03 VARCHAR(255) default '0',
level_04 VARCHAR(255) default '0',
level_05 VARCHAR(255) default '0',
level_06 VARCHAR(255) default '0',
level_07 VARCHAR(255) default '0',
level_08 VARCHAR(255) default '0',
level_09 VARCHAR(255) default '0',
thumb_seq INT(11) default '0',
cur_timestamp TIMESTAMP (8)
);
load DATA INfile 'c:/Inetpub/[URL unfurl="true"]wwwroot/k4web/pdf_proofs/filelist_process.txt'[/URL]
IGNORE INTO TABLE k4ngm.test_table
FIELDS TERMINATED BY '\\' OPTIONALLY ENCLOSED BY '' ESCAPED BY '$' LINES TERMINATED BY '\n';
UPDATE test_table
SET thumb_seq = IF (level_08 = NULL, '0', LEFT(LEFT(RIGHT(level_08, 8),9), 3));
It fails at the update. I get a 1366 error (Incorrect integer value: '' for column 'thumb_seq' at row 1). The 'level_08' column data is a string and I'm pulling a numeric value from that string and setting it to the 'thumb_seq' column, where it needs to be a number (and sortable as a number). Where am I going wrong?