I need to remove multiple rows from multiple tables using the foreign key

I have three different tables, tbl1, tbl2, and tbl3 for example. I need to delete rows from each of the three tables. Only problem is, there are foreign key constraints between each of the tables. If I delete a row from tbl1, the delete cascades and deletes all rows from from tbl2 and tbl3 that referenced the deleted row in tbl1. How can I delete only specific rows?

Here's one approach:

 DELETE FROM tbl3 WHERE tbl3.tbl1_id = 'foo';
 DELETE FROM tbl2 WHERE tbl2.tbl1_id = 'foo';
 DELETE FROM tbl1 WHERE tbl1.tbl1_id = 'foo';

How to get all data from 2 tables using the foreign key

This is the result of separating a single table in two: Table users: user_id (pk, ai) email password last_login Table data: user_id (fk to users.user_id) data_1 data_2 To select a single record when there was only one table: SELECT users.email, users

Left join multiple tables if the foreign key is not null - mysql optimization

There are some similar questions, but none of them matches my case. SQL Optimization - Join different tables based on column value How to JOIN on different tables based on column value MySQL query to JOIN tables based on column values MySQL: Use CASE

Selecting duplicate rows from a table (ignoring the primary key)

I have an Oracle table where I want to find out if there are any duplicate rows (i.e. where all column values are equal). The problem is that the rows have unique primary keys so I want to exclude them since they are basically preventing me doing thi

How to display the value from another table using the primary key

I have implemented my project in Yii. I displaying search results in view part. I wrote a search query for fetching the results from one table which is Recipe. In this table name, course_id, cuisinename,type, colorie_count respectively, course_id,cui

Insert values into a table from multiple tables using the sqlite query

If I have Table1 as A B C 1 b.1 c.1 2 b.2 c.2 1 b.3 c.3 My second table Table2 as A D E F G 1 d.1 e.1 f.1 g.1 2 d.2 e.2 f.2 g.2 I need to insert into an empty Table3 the values from above such that it looks like this. A B C D E 1 b.1 c.1 d.1 e.1 2 b.

How do I remove duplicate rows from a table without naming columns in the query?

I am tracking history of changes to rows in a table that is filled with a trigger on update of another table. It tracks the revision history of the main table. Often, my users, out of habit, will hit the SAVE button even though they have not changed

MSSQL query to remove duplicate rows from a TABLE by leaving separate rows in the table

I need to delete all duplicate rows except the first occurrence of similar rows from a Table Log having the same customer_id and deactivation_date. How do I achieve that with minimum number of sql statements. I Am using MS SQL Server 2008 express edi

Foreign key constraint problem when removing multiple tables using linq to sql

I have two tables with a foreign key constraint how can I delete rows from both of them in one transaction? linq to SQL seems to call my deletes in the wrong order. Is there somewhere I can check and make sure the constraint is recognized properly by

Expand the PHP function to retrieve a variety of queries from the mySQL database and correctly traverse multiple tables via their foreign key relationship

I am trying to build a robust php function that allows me to traverse over my normalized database. My mySQL database has 6 tables with the following column names (I am only including the primary and foreign keys, as well as some limited table columns

Related Articles