Delete a value from a column referred to two different table

Question

How would you delete a value from a column in a table when that column value refers to two different tables. Need a SQL Query here using Joins

Mehreen Tahir · Answer

You can use INNER JOIN clause in the DELETE statement to delete rows from a table and the matching rows in another table. For example, to delete rows from both Table1 and Table2 tables that meet a specified condition, you use the following statement. DELETE Table1, Table2 FROM Table1 INNER JOIN Table2 ON Table1. key = Table2. key WHERE // some condition; If you just want to delete rows from Table1, omit Table2 from in-between DELETE and FROM keywords. You can use other JOIN clauses as well. This time let me show you an example. DELETE customers FROM customers LEFT JOIN orders ON customers.customerID = orders.customerID WHERE orderNo IS NULL ; This query will remove customers who have not placed any order from customers table. Hope this helps.

Jignesh Kumar · Answer

Hi, First you need to delete from child table and then you need to delete from main table. For these you can use DELETE CASCADE for main table so once you delete records from main table then it will delete child table record automatically but before using that you should read below article. https://dba.stackexchange.com/questions/153351/delete-statement-conflicted-with-the-reference-constraint ALTER TABLE tb_state WITH CHECK ADD CONSTRAINT FK_tb_ChildTableName_tb_countr FOREIGN KEY (FK ID) REFERENCES tb_MAINTableName (PK ID) ON DELETE CASCADE And As per your requirement you cans use Join also Please find below link, http://www.mysqltutorial.org/mysql-delete-join/ https://stackoverflow.com/questions/43335944/how-to-delete-from-2-tables-using-join-query-in-sql-server

Delete a value from a column referred to two different table

Answers (2)