delete query

vbnetcoder
vbnetcoder used Ask the Experts™
on
I have a table with empty rows.  What would be the best way to delete the rows where all the records are empty (other then the primary key which will always have a value?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>What would be the best way to delete the rows where all the records are empty
That depends on what 'are empty' means, if that means NULL values or an empty string ""

<Might want to make a backup before actually executing these>

For starters..
DELETE * FROM YourTable WHERE Nz(Column1, "") = ""

If you just want to delete all rows and not test if they are empty or not...
DELETE * FROM YourTable

Author

Commented:
so if there are more then one columns i would do something like this?

DELETE * FROM YourTable WHERE Nz(Column1, "", Column2, "", Column3, "") = ""
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Commented:
you're on the right track..

DELETE * FROM YourTable
WHERE Nz(Column1, "") = "" AND Nz(Column2, "") = '"" AND Nz(Column3, "") = ""

Again, this will vary based on the definition of 'are empty'.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
By empty i mean that they have no value. There is nothing in them
Top Expert 2016

Commented:
to delete records where any field is empty or null aside from the Primary key field,
you only need to use a single field as  the criteria

delete * from tableX
where [column1] & ""=""

no need to add the other empty fields, since they are all empty or null

Author

Commented:
Rey,

They would not ALL be empty.  Wouldn't your code delete the rows where column1 is empty even if the other records for that row have a value?  I believe i do have to name them as suggested by Jim.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
I would agree with Jim, probably better to be safe with a WHERE clause that includes all on the non PK fields.

@vbNetCoder,

Just so you understand why Jim and Rey are making those particular syntax recommendations .  If you know that the fields with "no data" are actually NULL, then you could use:

DELETE FROM yourTable WHERE (Column1 IS NULL) AND (Column2 IS NULL) AND (Column3 IS NULL)....

but if those fields could have an empty string, then testing for:

(Nz(Column1, "") = "")

or

([Column1] & "" = "")

will select records where the field is either NULL, or contains an empty string.

Dale

Author

Commented:
i do believe they are empty string and not null
Top Expert 2016

Commented:
@vbnetcoder,

here is your statement above

What would be the best way to delete the rows where all the records are empty  (other then the primary key which will always have a value?
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Since I don't see any column names and sample data in this question, or logic for what's empty and what's not, and the exact definition of empty, the below code is not intended to be a literal solution but an example that requires you to replace YourTable, Column1, Column2, Column3, with the actual names.

DELETE * FROM YourTable
WHERE Nz(Column1, "") = "" AND Nz(Column2, "") = '"" AND Nz(Column3, "") = ""

Since I can't connect to your data source and view the table schema and the data, like Barney TBPD I had to use my imagination.

Author

Commented:
ty

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial