OraFAQ Forum: SQL & PL/SQL » How to check entire table count before delete

Home » SQL & PL/SQL » SQL & PL/SQL » How to check entire table count before delete

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to check entire table count before delete [message #406589]

Thu, 04 June 2009 15:09

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

Report message to a moderator

Re: How to check entire table count before delete [message #406590 is a reply to message #406589]

Thu, 04 June 2009 15:12

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: How to check entire table count before delete [message #406591 is a reply to message #406589]

Thu, 04 June 2009 15:13

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to check entire table count before delete [message #406592 is a reply to message #406589]

Thu, 04 June 2009 15:14

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Take a look at WHERE EXISTS

Report message to a moderator

Re: How to check entire table count before delete [message #406596 is a reply to message #406592]

Thu, 04 June 2009 16:56

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 ?

Report message to a moderator

Re: How to check entire table count before delete [message #406597 is a reply to message #406589]

Thu, 04 June 2009 17:01

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: How to check entire table count before delete [message #406599 is a reply to message #406589]

Thu, 04 June 2009 17:56

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.

Report message to a moderator

Re: How to check entire table count before delete [message #406605 is a reply to message #406599]

Thu, 04 June 2009 22:28

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

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

Report message to a moderator

Re: How to check entire table count before delete [message #406608 is a reply to message #406599]

Thu, 04 June 2009 23:36

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: How to check entire table count before delete [message #406749 is a reply to message #406589]

Fri, 05 June 2009 10:42

prasad.kshirsagar
Messages: 4
Registered: June 2009
Location: Houston

Junior Member

Leave it guys.

Thanks for all your nice words & help. Laughing

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 20 08:58:00 CDT 2025