Delete rows from one table based on multiple tables

0
votes

I have three tables Table1, Table2 and Table3 and the following query which deletes rows in Table2

delete from Table2 
where EXISTS
(select (1) from Table1
 where Table1.col1=Table2.col1
 AND   Table1.col2=Table2.col2
 AND   Table1.col3=(select **Table3.col3 from Table3** inner join Table2 on Table3.col1=Table2.col1)

If col3 of Table1 matches with col3 of Table3 and col1,col2 of Table1 matches with col1,col2 of Table2 then I need to delete the row from Table2. However I am unable to use Table3 in this query. Please help

2
sqldatabaseoracleoracle11goracle-sqldeveloper
Do you get any errors? – GolezTrol
@GolezTrol Yes, it says ORA-01427: single-row subquery returns more than one row – user2133404
Allright, that's important to mention. I meand that a subselect (you have two of those, so it might be either one) returns more than one row while it shouldn't. The first subselect is after exists, so that is allowed to have multiple rows. The innermost select on Table3, however, is used in a comparison using the = operator. This subquery is not allowed to return more than one row and is causing the error. Since the query in your question isn't syntactically correct, it would help if you posted the actual query, the table structure and some actual example data. – GolezTrol
@GolezTrol Please find this sql fiddle demo which I attempted sqlfiddle.com/#!4/71f68/1 I feel like I should limit the subquery to return one row, but I am not getting a clear picture – user2133404
Have you tried any of the two answers that were already given? Did they help you, and if not, can you comment on them to tell why they didn't? – GolezTrol

2 Answers

1
votes

Something like this should do the trick:

delete from 
    Table2 t2
where 
    Exists (
        select
            'x'
        from 
            Table1 t1 
                inner join
            Table3 t3
                on t1.col3 = t3.col3
        where
            t1.col1 = t2.col1 and
            t1.col2 = t2.col2
 );
0
votes

You might benefit from using the merge into statement. It's hard to distinguish every relationship in your Table1,2,3 and Col1,2,3 example names, but it might look like this:

merge into Table2 t2
using
  (select
    t2.id
  from
    Table1 t1
    inner join Table2 t2 on t2.col1 = t1.col1 and t2.col2 = t1.col2
    inner join Table3 t3 on t3.col3 = t1.col3 and t3.col1 = t2.col1
  ) t2x
on (t2.id = t2x.id)
when matched then
  delete;

Which is basically the same as

delete from Table2 t2 
where
  t2.id in
    (select
      t2.id
    from
      Table1 t1
      inner join Table2 t2 on t2.col1 = t1.col1 and t2.col2 = t1.col2
      inner join Table3 t3 on t3.col3 = t1.col3 and t3.col1 = t2.col1
    )