C.R.Vegelin wrote:
Hi everybody,
I defined a field Date_End with default NULL, but am missing it.
CREATE TABLE Regions
( Country CHAR(4) NOT NULL,
Date_Start CHAR(4) NOT NULL,
Date_End CHAR(4) default NULL, # this one ...
Description CHAR(50)
) Engine = MyISAM;
Regions table is filled with a tab-delimited input table like:
0001 1997 2500 France
1000 1976 WORLD
1010 1976 INTRA-EUR
LOAD DATA INFILE '../Regions.txt' INTO TABLE Regions
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
You have empty strings, '', not NULLs in column 3. NULLs are indicated by \N
for use with LOAD DATA INFILE. See the manual for all the details
<http://dev.mysql.com/doc/refman/5.0/en/load-data.html>.
SELECT * FROM Regions WHERE Country >= 1000;
+---------+------------+----------+-------------+
| country | date_start | date_end | description |
+---------+------------+----------+-------------+
| 1000 | 1976 | | WORLD |
| 1010 | 1976 | | INTRA-EUR |
| 1011 | 1976 | | EXTRA-EUR |
etc.
Question: why is NULL not shown for date_end ?
Because date_end = '', not NULL, for those rows.
DELETE FROM Regions WHERE Country >= 1000 AND Date_End < 2001;
Query OK; 33 rows affected;
SELECT * FROM Regions WHERE Country >= 1000;
Empty Set
Question: So NULL values are matching Date_End < 2001 in the DELETE ???
No, '' is interpreted as 0 in numeric context, hence it is less than 2001.
I am using MySQL version 5.0.15-nt. Help will be appreciated.
Cor Vegelin
Either use \N where you want NULLs in your Regions.txt file, or fix them after
importing. Fixing them is probably just a matter of
UPDATE Regions
SET date_end = NULL
WHERE Country >= 1000 AND Date_End = '';
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
