[SOLVED] How do I delete multiple (empty) rows in one table using phpMyAdmin?

[stevieontario]

Hello mysql experts,

 

I feel a bit dumb because I'm sure this is easy. But I want to delete a range of empty rows from a mysql table. I'm in phpMyAdmin, in Browse view.

 

To a greenhorn like me, it seems that clicking on the "Check All" feature at the bottom of the table, then clicking on the big red "x" (delete), would do the trick.

 

But no dice. It only deletes a single row at a time. (It does work with rows that have data, but not the empty ones.)

 

There aren't a lot of rows, only 363, but there's got to be an easier way to do this than one at a time.

 

Can anyone help? Thanks!

[revraz]

Define empty?

 

Can't you just do a DELETE statement in SQL instead of using the GUI?

[stevieontario]

thanks for the prompt reply.

 

Definition of empty: good question. This is a table that will only ever have 124 rows. I'll populate some fields by hand, but other fields I'll populate using an INSERT query.

 

So I tested the INSERT query once, successfully, and it populated one field. So the result was a 124-row table, with one column populated.

 

Then I realized I could populate another column using INSERT. This didn't populate the column I wanted, but it did add another 124 rows to the table. These are what I mean by empty.

 

 

[revraz]

They are not really empty if you have rows, so what are you actually creating, just a ID?  Something has to be in that row to be a row right?

 

IF a row exists, use UPDATE and not INSERT to modify the data in it.

[stevieontario]

thanks revraz,

 

I guess you're right, if the rows exist they can't be empty. Two of the fields are "int" type, so those contain zeros, which I guess are auto-generated. The column I wanted to populate stayed empty after my failed query, which is part of the reason why I assumed the rows were empty.

 

Another reason is that I was able to select the populated rows for deletion using "check all" and phpMyAdmin indicated a range of rows in that query box above the table (in Browse view). But the query box only indicates a single row when I click "check all" in the rows that only contain the zeros.

 

 

[revraz]

They would only contain 0's if you populated them or if the Default is set to 0.

 

So let's get on point, what is it that you want to do exactly.

 

If you want to delete all the rows that have a 0 in a column, just do:

 

DELETE FROM table WHERE column = 0

[stevieontario]

Appreciate your time, revraz.

 

All rows have the default zeros. Half the rows also contain useful data (in one particular column, called "name") in addition to the default zeros. I want to delete the rows in which "name" is empty.

[Ken2k7]

DELETE FROM table WHERE name = '';

 

Define empty. What's empty for name?

[stevieontario]

Ken2k7,

 

Empty means blank. The zeros I guess are there because their respective columns ("id" and "intensity") are defined as INT and are not null.

[Ken2k7]

I assume blank is ''.

 

To verify the rows you delete are correct, run this SQL:

SELECT * FROM table WHERE name='';

 

If that returns all the rows you want to delete, then run this:

DELETE FROM table WHERE name = '';

[stevieontario]

It worked! Beautiful! Many thanks, appreciate your time.