delete query

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?
vbnetcoderAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornSQL Server Data DudeCommented:
>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
vbnetcoderAuthor Commented:
so if there are more then one columns i would do something like this?

DELETE * FROM YourTable WHERE Nz(Column1, "", Column2, "", Column3, "") = ""
Jim HornSQL Server Data DudeCommented:
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'.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

vbnetcoderAuthor Commented:
By empty i mean that they have no value. There is nothing in them
Rey Obrero (Capricorn1)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
vbnetcoderAuthor 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 LLCCommented:
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
vbnetcoderAuthor Commented:
i do believe they are empty string and not null
Rey Obrero (Capricorn1)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 DudeCommented:
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.
vbnetcoderAuthor Commented:
ty
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.