MySQL DELETE query not working (null problem)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
korngold
Forum Newbie
Posts: 11
Joined: Wed Nov 02, 2005 11:49 am

MySQL DELETE query not working (null problem)

Post 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!!!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
korngold
Forum Newbie
Posts: 11
Joined: Wed Nov 02, 2005 11:49 am

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
korngold
Forum Newbie
Posts: 11
Joined: Wed Nov 02, 2005 11:49 am

Post 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.
Post Reply