CODEWITHSUNDEEP
sql
calev
calev

Reputation: 55

SQL, How to delete rows from related tables using a query?

I have the following tables

    vehicle (veh_num(PK), veh_desc)
    log (log_num(PK), veh_num(FK), log_date, log_complaint)
    log_line (log_num(PK,FK), logline_num(PK), emp_id(FK), logline_date,logline_action)
    part (part_code(PK), logline_num(FK), log_num(FK), part_desc)
    signout (signout_num(PK), part_code(FK), emp_id(FK), log_num(FK), signout_date)

I want to run a query which will delete all the records in the vehicle table with for instance, veh_num = "EK458" and also delete rows which are related to the veh_num in the other tables.

I have started with the following query, 

    DELETE FROM signout WHERE EXISTS
    (select * from vehicle,log,log_line,part
    where 
    vehicle.veh_num = 'EK458'  AND
    vehicle.veh_num = log.veh_num AND
    log.log_num = log_line.log_num AND
    log_line.log_num = part.log_num AND 
    part.part_code = signout.part_code);

This query deletes all the associated values of veh_num = "EK458" in the signout table, however, I want a query which will delete the rows from the all the tables which are related to veh_num. Thanks in advance

Upvotes: 1

Views: 26215

Answers (3)

unqualified
unqualified

Reputation: 131

If you have to use a query, you'll need to write multiple delete queries, deleting first from the outermost tables of the relationship hierarchy and working your way in, often in layers/sets of tables, to the primary table.

Answered here too: How do I delete all related records?

Upvotes: 0

Naeem
Naeem

Reputation: 36

Read this link http://beginner-sql-tutorial.com/sql-integrity-constraints.htm and have you tried join instead of subquery in query?

Upvotes: 0

SamiHuutoniemi
SamiHuutoniemi

Reputation: 1595

I think what you want to do is having the delete cascade into other tables.

Take a look at this How do I use cascade delete with SQL Server?

Upvotes: 3

Related Questions