Home » SQL & PL/SQL » SQL & PL/SQL » How to check entire table count before delete
How to check entire table count before delete [message #406589] Thu, 04 June 2009 15:09 Go to next message
prasad.kshirsagar
Messages: 4
Registered: June 2009
Location: Houston
Junior Member
Hello

I have a simple doubt

I am deleting records from one table, but before deleting records I want to make sure that count(*) of another table should not be null.

THe query is like


delete from mi000cht00
where (corpnum like 'B%' or
corpnum like 'LC%') and <working_table is not null>;

In this SQL stmt, I want to check <working_table is not null> part.


please help
Thanks
Re: How to check entire table count before delete [message #406590 is a reply to message #406589] Thu, 04 June 2009 15:12 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> I want to make sure that count(*) of another table should not be null.

COUNT can never be NULL; COUNT is either 0 or greater than 0.
Re: How to check entire table count before delete [message #406591 is a reply to message #406589] Thu, 04 June 2009 15:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If with "not null" you mean not empty you can use:
and exists (select null from mytable where rownum=1)

By the way, count(*) is ALWAYS not null, unless you mean 0.

Regards
Michel
Re: How to check entire table count before delete [message #406592 is a reply to message #406589] Thu, 04 June 2009 15:14 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Take a look at WHERE EXISTS
Re: How to check entire table count before delete [message #406596 is a reply to message #406592] Thu, 04 June 2009 16:56 Go to previous messageGo to next message
prasad.kshirsagar
Messages: 4
Registered: June 2009
Location: Houston
Junior Member
Thank you so much for the replies.
Appreciated !

I thought to use of WHERE EXIST condition, but the database will take more resources & time for EXISTS condition.


Is there any alternate other than this ?
Re: How to check entire table count before delete [message #406597 is a reply to message #406589] Thu, 04 June 2009 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
> the database will take more resources & time for EXISTS condition.
More resources as compared to what?

Please clarify the requirements.
> I want to make sure that count(*) of another table should not be null.
Please provide test cases for all conditions.
Re: How to check entire table count before delete [message #406599 is a reply to message #406589] Thu, 04 June 2009 17:56 Go to previous messageGo to next message
prasad.kshirsagar
Messages: 4
Registered: June 2009
Location: Houston
Junior Member
Ok...Let me try to explain.

I want to delete data from table mi000cht00. Now There is another working table say mi_cht_working, which contains Column 'Corpnum'.

Now while deleting from mi000cht00, I want to add condition WHERE Corpnum of Working table is NULL.

The SQL stmt will be something like this:

delete from mi000cht00
where (corpnum like 'B%' or
corpnum like 'LC%') AND mi_cht_working.Corpnum is not null;


Hope this helps you.

Thanks.
Re: How to check entire table count before delete [message #406605 is a reply to message #406599] Thu, 04 June 2009 22:28 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
What is the relation between mi000cht00 and mi_cht_working?

Test case doesn't mean this.
You have to give scripts including table creation and data insertion.

By
Vamsi
Re: How to check entire table count before delete [message #406608 is a reply to message #406599] Thu, 04 June 2009 23:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
WHERE Corpnum of Working table is NULL.

You didn't read what I said.
A table is NOT null or not null. A table exists or not. A table contains row(s) or not.
Take care of the language you use. This is meaningless.

You already got the solution to your problem (assuming we are right in the assumptions we made to correct your vocabulary).

Regards
Michel
Re: How to check entire table count before delete [message #406749 is a reply to message #406589] Fri, 05 June 2009 10:42 Go to previous message
prasad.kshirsagar
Messages: 4
Registered: June 2009
Location: Houston
Junior Member
Leave it guys.

Thanks for all your nice words & help. Laughing

Previous Topic: PL/SQL: ORA-00942 Error
Next Topic: Supressing count on timestamp
Goto Forum:
  


Current Time: Sun Dec 04 12:53:55 CST 2016

Total time taken to generate the page: 0.07371 seconds