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.