Page 1 of 1

MySQL DELETE query not working (null problem)

Posted: Thu Nov 10, 2005 3:35 pm
by korngold
Hi all,

I have a temp table with a large number of null values. If the entire row is null, I want to delete that row. If any cells are populated, I want to leave the entire row as is. I'm not having luck with the following (hacked-up MS Access) code. Any help is greatly appreciated.

Code: Select all

DELETE FROM tmptable WHERE ((tmptable.title1 is NULL) AND (tmptable.event1 is NULL) AND (tmptable.recval1 is NULL) );
Thank you in advance!!!

Posted: Thu Nov 10, 2005 3:47 pm
by feyd
maybe they aren't null? post your table's structure and some of the records that should and shouldn't match (SQL export) and what you are getting..

Posted: Thu Nov 10, 2005 3:54 pm
by korngold
feyd,

Thanks for the quick response. The table is being created using this statement (through PHP):


Code: Select all

$tmpsql3 = "CREATE TABLE `tmptable` (
  `julian` int(11) NOT NULL default '0',
  `title1` varchar(50) character set ascii collate ascii_bin default NULL,
  `event1` int(11) default NULL,
  `recval1` varchar(50) character set ascii collate ascii_bin default NULL,
  PRIMARY KEY  (`julian`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
In PHP Admin, when I browse the file, all the rows (I'm trying to delete) are NULL all the way across, aside from the first column (Julian). Unfortunately, I can't do a data export (sensitive data). Here is the error I'm getting when I try to run the process from PHP:
Error! Could not delete data: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; DELETE FROM tmptable WHERE ((tmptable.title1 is NULL
Thanks again,
Anthony

Posted: Thu Nov 10, 2005 4:00 pm
by feyd
you see the semi-colon in the "check near" part of the error? That shouldn't be there.. sounds like you're accidentaly sending the wrong query..

Posted: Thu Nov 10, 2005 4:05 pm
by korngold
feyd,

Thanks a lot! I had a "use dbname; " before the DELETE. Apparently, it never got past that. I've changed just about everything after that semicolon for the past hour! <doh>

Thanks again.