Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update problem

Status
Not open for further replies.

CryoGen

MIS
Apr 15, 2004
86
US
Hello all,

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?
 
Disregard. The update script works. I had to reset the my.ini file to turn off strict mode and it updated just fine.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top