List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:March 17 1999 7:01pm
Subject:Re: INSERT ignoring NULLs
View as plain text  
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

Thread
INSERT ignoring NULLsGreg Kelley17 Mar
  • Re: INSERT ignoring NULLsChristian Mack17 Mar
  • INSERT ignoring NULLsMichael Widenius30 Mar