SQL Delete query with INNER join?

Question

I am trying to make a delete query that will basically do the following.

Someone will enter a career-name, the query must delete the entire row from the careers table, and it must delete the entire row in the link table. (link table connects two tables together)

I keep getting "Specify the table containing the records you want to delete."

Here is what the SQL looks like so far:

delete * from link, careers
where link.careerid = careers.id
and careername = 'administrative assistant'

Ive uploaded the database to zippyshare if you want to take a look: http://www25.zippyshare.com/v/44933542/file.html

Any help will be greatly appreciated :)

Answer 1

Yasser isn't completely correct...there is one way this could be done with a single delete. If you define a foreign key constraint between the two tables and specify relational integrity with cascade delete, then deleting a row from the primary table will automatically delete all rows in the subsidiary table containing the deleted foreign key value.

Answer 2

you cant issue a delete statement on two tables, since the delete statement here is issued on the join of the two tables not the tables themselves, so you are issuing a delete on a table that doesnt really exist in your database (rather than on a table that was created by the database software on the fly as the result of the join)



if you want to delete more than one row in more than one table then you should issue two delete statements like

******************************

delete * from link where careerid in ( select id from careers2 where careername = 'administrative assistant')

*****************************

then

*****************************

delete * from career where careername = 'administrative assistant';

*****************************



how ever in your case, there is no need for all of this, you should simply make your careerid a primary key in the Career table linked to the id key in the Link table.



and in the relationship scheme (in database tools) make sure that the link between the Career and Link to have Reinforce Referencial Integrity and Cascade on Delete checked)



so! when you delete the master record the slave record will get deleted as well.



so you will end up with a simple delete statement of the form:

delete * from career where careername = 'administrative assistant';

and the linked records in link table will be cascade deleted.

Answer 3

How did you specify the relational integrity regulations for the toddler table relationships? in case you distinct CASCADE DELETE, then deleting rows out of your parent tables additionally should delete the corresponding rows from the toddler table. otherwise, you will desire to delete the rows from the toddler table first and then delete from the parent tables. of direction, in case you opt to delete each and every thing, you may only DROP the tables (and then redefine them in case you choose the emply tables to exists...an option to this is to verify no rely if Deplhi helps the TRUNCATE verb, which will do the comparable in one step.)