delete multiple rows from different tables on oracle

Question

I have a two table.One of them is student, the other one is salary.

Student table

  id  | name   | code | status
  1   | steven | 123  | 100
  2   | joe    | 678  | 200
  3   | paul   | 758  | 100

Salary table

 id  | code | status |  currency
  1   | 123  | 100    |  euro
  2   | 678  | 200    |  dolar
  3   | 758  | 520    |  yuan

I want to delete row1 from Student table and row 1 and 2 from Salary table because code and status fields are same.

I write that query

delete a,b Student as a , join Salary as b  
on a.code= b.code and a.status = b.status

but it is not working.I want to delete rows with one query.Do you have any idea?

Wait a minute. How is student 1 (Steven) related to row with ID = 2 in SALARY table whose CODE = 678 and STATUS = 200? True, they exist in STUDENT table, but there's no other relation whatsoever. Does it mean that regardless of which row you delete from the STUDENT table, you want to delete all rows in SALARY whose (CODE, STATUS) combination exists in STUDENT? That doesn't make any sense to me. — Littlefoot
– Littlefoot, Commented Mar 25, 2021 at 12:43
I want to delete rows that have same code and status values from tables — tom
– tom, Commented Mar 25, 2021 at 12:45
Is it possible to have any code in salary table which is not available in student table? — Kazi Mohammad Ali Nur Romel
– Kazi Mohammad Ali Nur Romel, Commented Mar 25, 2021 at 12:46

Littlefoot · Accepted Answer · 2021-03-25 12:52:27Z

1

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>

answered Mar 25, 2021 at 12:52

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

tom Over a year ago

Thank you very much.That is exactly what i want

Kazi Mohammad Ali Nur Romel · Accepted Answer · 2021-03-25 12:58:16Z

0

You can use two statements to do so easily:

delete student s where exists(
select * from student stu inner join salary sal on stu.code=sal.code and stu.status=sal.status and stu.id=s.id);

delete salary sal where not exists (select code from student stu where stu.code=sal.code);

First one to delete all the students having same code and status in both tables and second is to delete all the rows from salary table where code doesn't exist in student table.

answered Mar 25, 2021 at 12:58

Kazi Mohammad Ali Nur Romel

16.2k2 gold badges17 silver badges29 bronze badges

Collectives™ on Stack Overflow

delete multiple rows from different tables on oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related