* Pushkar Pradhan > I am using mysql 3.23.49 solaris platform. > I used load data local infile command to load tables. > I got NULL entries in my rows (i.e. some rows have null in all fields) and > since I executed this command twice I got same entries twice. > > I tried to remove null rows by doing: > delete from book where subject='NULL'; > But it didn't work, also how to delete identical rows?
NULL is a special value, not a string, so you should not use quotes: delete from book where subject=NULL; The best way to _avoid_ duplicate entries is by using an unique index, preferrably a primary key. To _delete_ rows, you must normally use DELETE, but the following four steps can also be used to delete duplicates: 1. Create an empty duplicate of the table: CREATE TABLE newtable SELECT * FROM oldtable WHERE 1=2; 2. Create a primary key or a unique key on the new table: ALTER TABLE newtable ADD PRIMARY KEY (id); If there is no id field (or any single field which should be unique), you can include all the fields you need to make the row unique using this syntax: ALTER TABLE newtable ADD UNIQUE (field1,field2,field3...); 3. Insert the data from the old table: INSERT INTO newtable SELECT * FROM oldtable; 4. Rename the tables: ALTER TABLE oldtable RENAME delete_me_later; ALTER TABLE newtable RENAME oldtable; <URL: http://www.mysql.com/doc/en/ALTER_TABLE.html > hth, -- Roger --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
