How can I combine these quries into one SQL query?
I want to be able to delete from multiple table in one statement...

DELETE FROM COMPANYS
WHERE company_id <> 5 and company_id <> 41 and company_id <> 20;

DELETE FROM DIVISIONS
WHERE company_id <> 5 and company_id <> 41 and company_id <> 20;

DELETE FROM PLANS
WHERE company_id <> 5 and company_id <> 41 and company_id <> 20;

You can't.

By definition, the DELETE statement deletes row from a single table only.

Why do you think you need to do this in a single statement?

Wrap your multiple statements in a transaction if you need to be sure they all complete atomically, or are all rolled back if they don't.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Jeff,
What do you mean by wrapping multiple statements in a transaction?
Could you please explain?

Thank you!

Managing transactions is generally easier to do in the host language, IMO. There, you would define a transaction on the connection to the database, begin the transaction, execute the stored procedure that contains your delete statements (or execute them individually), then on success commit the transaction or on an exception rollback the transaction, undoing all effects of the SQL statements you've executed.

This is the SQL Language forum; while the basic transaction mechanism is part of the SQL Standard, the implementation details (especially error handling and flow control) may differ between particular products.

You define and begin a transaction with the BEGIN TRANSACTION statement, commit the transaction with the COMMIT TRANSACTION statement, and rollback the transaction with ROLLBACK TRANSACTION.

The awkward part comes in detecting whether to do a COMMIT or ROLLBACK. In SQL Server's T/SQL language, the @@ERROR variable can be tested after each statement. If the value is nonzero, an error has occurred so you ROLLBACK the transaction. If all statements succeed, you COMMIT instead.

There is a whole other issue regarding the locking of data while the delete is being executed. Not only does a transaction allow you to declare a set of UPDATE/DELETE statements that should execute atomically, you can also control how others may, or may not, view the data while you are in the process of changing it, or whether or not you "see" any changes that they may be making.

That is called the transaction isolation level and that is a subject for another post.

Code to manage transactions (in T/SQL) can be structured like this:
Like I said, simpler in the host...




Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

DELETE FROM COMPANYS C1,DIVISIONS D1,PLANS P1
WHERE c1.company_id=d1.company_id=p1.company_id
AND company_id <> 5 and company_id <> 41 and company_id <> 20;

It is maybe right. you can try i think the PL/SQL is not good way. because it is very slow.