Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

delete from nested tables... plz!!!

Status
Not open for further replies.

chapoor

Programmer
Jan 18, 2005
2
SE
Hi,

I cant get this query to work with MySQL.

delete from
companies as com,
employees as emp,
informations as inf
where
emp.companyId=com.id
and
inf.companyId=com.id
and
com.enabled=0;

I ve this tables...
Company
------------
Id Name Enabled
1 acme 0
2 mysql 1

Employees
-------------
Id Name CompanyId
1 Smith 1
2 Will 1
3 Cage 2

Information
-------------
Id Description CompanyId
1 some-info 1
2 some-info 2

I want to delete:
company id 1 row, smith and will rows, 'some-info' (1) row.

Please help me!!!
 
If you don't have cascading delete triggers, simply use 3 queries:
DELETE FROM Information WHERE CompanyId=1;
DELETE FROM Employees WHERE CompanyId=1;
DELETE FROM Company WHERE Id=1;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
I want it to be dynamically without need to specify the company Id, there must be some solution?

I think I was not that clear before, let me try again.

a Company can have zero (null) or many employees.
a Comapny can have zero (null) or one information.

When a company is disabled (enabled=0) then it shall delete all employee rows and information connected to the company.
If no employees or information exist then it only deletes the company row it self.

I hope my explaination is more understandable now.

Thanks again.
 
Something like this ?
DELETE FROM Information WHERE CompanyId IN (SELECT Id FROM Company WHERE enabled=0);
DELETE FROM Employees WHERE CompanyId IN (SELECT Id FROM Company WHERE enabled=0);
DELETE FROM Company WHERE enabled=0;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
try this --
Code:
delete companies,employees,informations
  from companies,employees,informations
 where companies.enabled = 0
   and companies.id = employees.companyId
   and companies.id = informations.companyId
remember, always back up your database before trying somebody's update or delete sql

:)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top