How to Delete Multiple Rows in Diferent Tables

MySQL delete multiple rows in multiple tables

One way you can delete from multiple tables if you introduce foreign key constraints with ON DELETE CASCADE.

This is the other way around:

DELETE C,SC,I
FROM categories C 
INNER JOIN sub_categories SC ON C.id = SC.category_id
INNER JOIN items I ON SC.id = I.subcategory_id
WHERE C.id = 5;

Check this Delete with join(multiple tables)

EDIT:

If sub categories don't have any item under it then you need to replace the last INNER JOIN by LEFT JOIN

DELETE C,SC,I
FROM categories C 
INNER JOIN sub_categories SC ON C.id = SC.category_id
LEFT JOIN items I ON SC.id = I.subcategory_id
WHERE C.id = 5;

delete multiple rows from different tables on oracle

Would something like this do? PL/SQL, though, not SQL.

Initial data sets:

SQL> select * from student;

        ID NAME         CODE     STATUS
---------- ------ ---------- ----------
         1 steven        123        100
         2 joe           678        200
         3 paul          758        100

SQL> select * from salary;

        ID       CODE     STATUS CURREN
---------- ---------- ---------- ------
         1        123        100 euro
         2        678        200 dollar
         3        758        520 yuan

Remove common (CODE, STATUS) combinations:

SQL> begin
  2    for cur_r in (select code, status from student
  3                  intersect
  4                  select code, status from salary
  5                 )
  6    loop
  7      delete from student where code = cur_r.code and status = cur_r.status;
  8      delete from salary  where code = cur_r.code and status = cur_r.status;
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from student;

        ID NAME         CODE     STATUS
---------- ------ ---------- ----------
         3 paul          758        100

SQL> select * from salary;

        ID       CODE     STATUS CURREN
---------- ---------- ---------- ------
         3        758        520 yuan

SQL>

Deleting rows from multiple tables in MySQL

DELETE projects, images 
FROM projects, images 
WHERE projects.p_id = ? 
AND projects.p_id = images.p_id;

MySQL delete row from multiple tables

Yes, that is correct. It works fine here:

CREATE TABLE table1 (id int, username nvarchar(30));
CREATE TABLE table2 (id int);
CREATE TABLE table3 (id int);
CREATE TABLE table4 (id int);

INSERT INTO table1 VALUES (1, 'Foo'),(2, 'Bar');
INSERT INTO table2 VALUES (1),(2);
INSERT INTO table3 VALUES (1),(2);
INSERT INTO table4 VALUES (1),(2);

SELECT COUNT(*) FROM table1;
2
SELECT COUNT(*) FROM table2;
2
SELECT COUNT(*) FROM table3;
2
SELECT COUNT(*) FROM table4;
2

DELETE t1, t2, t3, t4 FROM
  table1 as t1
  INNER JOIN  table2 as t2 on t1.id = t2.id
  INNER JOIN  table3 as t3 on t1.id=t3.id
  INNER JOIN  table4 as t4 on t1.id=t4.id
  WHERE  t1.username='Foo' AND t1.id='1';

SELECT COUNT(*) FROM table1;
1
SELECT COUNT(*) FROM table2;
1
SELECT COUNT(*) FROM table3;
1
SELECT COUNT(*) FROM table4;
1

If it's not working for you, perhaps you can modify this example to show what problem you are having.

delete rows from multiple tables

Well, if you had used InnoDB tables, you could set up a cascading delete with foreign keys that would do it all automatically. But if you have some reason for using MyISAM, You just use a multiple-table DELETE:

DELETE FROM boards, topics, messages
USING boards INNER JOIN topics INNER JOIN messages
WHERE boards.boardid = $boardid
    AND topics.boardid = boards.boardid
    AND messages.boardid = boards.boardid;

I need to delete multiple rows from multiple tables using foreign Key

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';

---

Related Topics