From: Christian Mack Date: March 17 1999 7:01pm Subject: Re: INSERT ignoring NULLs List-Archive: http://lists.mysql.com/mysql/443 Message-Id: <36EFFBF9.3922A3E7@compal.de> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Greg Kelley wrote: > > I have created a SQL script file that creates a table then populates it with > INSERT INTO table VALUES ('blah','blah','etc') > > MySQL is running under Redhat Linux v5.2 > > It appears that values of NULL cause subsequent field values to shift up and > fill in what should have been the NULL value. The script seems OK - is > there a problem with MySQL and scripts? > > The 5th field (expdt) instead of a getting NULL value is getting the 6th > value (28540) that belongs to field 6 (anlrt). I have 11 tables and over > 150,000 records to move from M$-Acce$$ to MySQL. > > Sorry for the large mail message, but here is part of the script: (thanks > for any insights...) > > CREATE TABLE HR1 > ( > usid CHAR(9) NOT NULL, > psnum CHAR(6), > pstyp CHAR(1), > birdt DATETIME, > expdt DATETIME, > anlrt REAL, > hrlrt REAL, > dayyr INT, > hrsyr INT, > ftebs CHAR(1), > fte INT, > vpr CHAR(2), > band CHAR(1), > amsrg CHAR(2), > exe INT, > clscd CHAR(5), > bench CHAR(1), > hegis CHAR(6), > agg CHAR(6), > shtxt CHAR(15), > octyp CHAR(1), > cltit CHAR(50), > entry CHAR(1), > arfrdt DATETIME, > clsyrs REAL, > aclsyrs REAL, > hire DATETIME, > tendt DATETIME, > fname CHAR(15), > mname CHAR(15), > lname CHAR(20), > rarea CHAR(3), > rorg CHAR(4), > objno CHAR(4), > arave REAL, > hrave REAL, > ams_band_arave REAL, > bench_arave REAL, > armpt INT, > hrmpt REAL, > ardef INT, > hrdef REAL, > sal_def INT, > bench_sal_def INT, > cls_sal_def INT, > hmin REAL, > hmean REAL, > hmax REAL, > hquart REAL, > amin INT, > amean INT, > amax INT, > aquart INT, > band_amean INT, > adj_amin INT, > adj_amean INT, > adj_amax INT, > adj_aquart INT, > tot_in_class INT, > min_hrlrt REAL, > avg_hrlrt REAL, > max_hrlrt REAL, > min_anlrt INT, > avg_anlrt INT, > max_anlrt INT, > bench_avg_anlrt INT, > fte_anlrt REAL, > aye_anlrt INT, > mark_pct INT, > emp_mark_pct INT, > bench_mark_pct INT, > over_min INT, > emp_sur_adj_anl INT, > sur_adj_anl INT, > emp_sur_adj_hrl REAL, > sur_adj_hrl REAL, > rpene INT, > pscd INT, > h3quart REAL, > a3quart INT, > adj_a3quart INT, > KEY (usid) > )\g > > INSERT INTO HR1 > VALUES ( > '012345678', > 'KAE067', > 'B', > '1988-07-01 00:00:00', > NULL, > 28540, > 0, > 196, > 0, > '6', > 75, > '15', > NULL, > NULL, > 1, > '00265', > '0', > NULL, > NULL, > NULL, > 'P', > 'HEALTH EDUCATOR/COUNSELOR', > 'N', > '1988-10-12 00:00:00', > 8.7, > 5.7, > '1988-10-12 00:00:00', > NULL, > 'JOHN', > NULL, > 'SMITH', > 'KSH', > 'KC24', > '0650', > 38797, > 0, > 0, > 0, > 37268, > 0, > -19, > 0, > -19, > 0, > 70, > 0, > 0, > 0, > 0, > 28920, > 36570, > 44210, > 32740, > 0, > 21690, > 27430, > 33160, > 24560, > 7, > 0, > 0, > 0, > 30290, > 36070, > 44200, > 0, > 38050, > 0, > 76, > 20, > 0, > 316, > 560, > 19474, > 0, > 0, > 597, > 2, > NULL, > 40390, > 30290 )\g > > ___________________________ > Greg Kelley Hi Greg Couldn't reproduce any errors with mysql 3.22.20a I get this: mysql> select * from HR1; +-----------+--------+-------+---------------------+-------+ | usid | psnum | pstyp | birdt | expdt | +-----------+--------+-------+---------------------+-------+ | 012345678 | KAE067 | B | 1988-07-01 00:00:00 | NULL | +-----------+--------+-------+---------------------+-------+ ------------+--------+-------+-------+-------+------+------+ anlrt | hrlrt | dayyr | hrsyr | ftebs | fte | vpr | ------------+--------+-------+-------+-------+------+------+ 28540.0000 | 0.0000 | 196 | 0 | 6 | 75 | 15 | ------------+--------+-------+-------+-------+------+------+ ------+-------+------+-------+-------+-------+------+-------+ band | amsrg | exe | clscd | bench | hegis | agg | shtxt | ------+-------+------+-------+-------+-------+------+-------+ NULL | NULL | 1 | 00265 | 0 | NULL | NULL | NULL | ------+-------+------+-------+-------+-------+------+-------+ -------+---------------------------+-------+---------------------+ octyp | cltit | entry | arfrdt | -------+---------------------------+-------+---------------------+ P | HEALTH EDUCATOR/COUNSELOR | N | 1988-10-12 00:00:00 | -------+---------------------------+-------+---------------------+ --------+---------+---------------------+-------+-------+-------+ clsyrs | aclsyrs | hire | tendt | fname | mname | --------+---------+---------------------+-------+-------+-------+ 8.7000 | 5.7000 | 1988-10-12 00:00:00 | NULL | JOHN | NULL | --------+---------+---------------------+-------+-------+-------+ -------+-------+------+-------+------------+--------+----------------+ lname | rarea | rorg | objno | arave | hrave | ams_band_arave | -------+-------+------+-------+------------+--------+----------------+ SMITH | KSH | KC24 | 0650 | 38797.0000 | 0.0000 | 0.0000 | -------+-------+------+-------+------------+--------+----------------+ -------------+-------+--------+-------+--------+---------+---------------+ bench_arave | armpt | hrmpt | ardef | hrdef | sal_def | bench_sal_def | -------------+-------+--------+-------+--------+---------+---------------+ 0.0000 | 37268 | 0.0000 | -19 | 0.0000 | -19 | 0 | -------------+-------+--------+-------+--------+---------+---------------+ -------------+--------+--------+--------+--------+-------+-------+-------+ cls_sal_def | hmin | hmean | hmax | hquart | amin | amean | amax | -------------+--------+--------+--------+--------+-------+-------+-------+ 70 | 0.0000 | 0.0000 | 0.0000 | 0.0000 | 28920 | 36570 | 44210 | -------------+--------+--------+--------+--------+-------+-------+-------+ --------+------------+----------+-----------+----------+------------+ aquart | band_amean | adj_amin | adj_amean | adj_amax | adj_aquart | --------+------------+----------+-----------+----------+------------+ 32740 | 0 | 21690 | 27430 | 33160 | 24560 | --------+------------+----------+-----------+----------+------------+ --------------+-----------+-----------+-----------+-----------+-----------+ tot_in_class | min_hrlrt | avg_hrlrt | max_hrlrt | min_anlrt | avg_anlrt | --------------+-----------+-----------+-----------+-----------+-----------+ 7 | 0.0000 | 0.0000 | 0.0000 | 30290 | 36070 | --------------+-----------+-----------+-----------+-----------+-----------+ -----------+-----------------+------------+-----------+----------+ max_anlrt | bench_avg_anlrt | fte_anlrt | aye_anlrt | mark_pct | -----------+-----------------+------------+-----------+----------+ 44200 | 0 | 38050.0000 | 0 | 76 | -----------+-----------------+------------+-----------+----------+ --------------+----------------+----------+-----------------+-------------+ emp_mark_pct | bench_mark_pct | over_min | emp_sur_adj_anl | sur_adj_anl | --------------+----------------+----------+-----------------+-------------+ 20 | 0 | 316 | 560 | 19474 | --------------+----------------+----------+-----------------+-------------+ -----------------+-------------+-------+------+---------+---------+-------------+ emp_sur_adj_hrl | sur_adj_hrl | rpene | pscd | h3quart | a3quart | adj_a3quart | -----------------+-------------+-------+------+---------+---------+-------------+ 0.0000 | 0.0000 | 597 | 2 | NULL | 40390 | 30290 | -----------------+-------------+-------+------+---------+---------+-------------+ 1 row in set (0.01 sec) Tschau Christian