• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 754
  • Last Modified:

How do you use inner joins on a delete statement using oracle

Hello All,
I am getting this error when I try to compile the following sql statements in ORACLE. It is telling me that the statement is not ended correctly. It is highlighting the first inner join. Can someone tell me what is wrong here

DELETE FROM batch_payment_schedule bps
    INNER JOIN w_batch_pledge_entry wbpe ON wbpe.bpe_batch_number = bps.batch_number
    AND                                      wbpe.bpe_number       = bps.bpe_number
    INNER JOIN batch_pledge_entry bpe ON bpe.bpe_batch_number = wbpe.bpe_batch_number
    AND                                  wbpe.bpe_number      = bpe.bpe_number
    WHERE (wbpe.bpe_payment_frequency = 'O'
    AND bpe.bpe_payment_frequency <> 'O'
    AND bps.bpe_schedule_status = 'U'
    AND wbpe.xoption IN (adv_declarations.c_modify));
0
bill_home
Asked:
bill_home
1 Solution
 
anumosesCommented:
DELETE FROM batch_payment_schedule bps
where .............
INNER JOIN w_batch_pledge_entry wbpe ON wbpe.bpe_batch_number = bps.batch_number

This has to be your sql
0
 
anumosesCommented:
delete from batch_payment_schedule bps
where wbpe.xoption IN  (
select wbpe.xoption
from ......
inner join w_batch_pledge_entry wbpe ON wbpe.bpe_batch_number = bps.batch_number
...............
inner join batch_pledge_entry bpe ON bpe.bpe_batch_number = wbpe.bpe_batch_number
.........................
WHERE ...............
AND .................)
0
 
bill_homeAuthor Commented:
Hello anumoses, you have two solutions...which one is it?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
anumosesCommented:
try the second one.
0
 
clayhopkinsCommented:
Hi Bill-

Another option:

SELECT * FROM batch_payment_schedule bps
    INNER JOIN
      (w_batch_pledge_entry wbpe INNER JOIN batch_pledge_entry bpe
       ON bpe.bpe_batch_number = wbpe.bpe_batch_number
       AND bpe.bpe_number = wbpe.bpe_number)
    ON wbpe.bpe_batch_number = bps.batch_number
    AND wbpe.bpe_number = bpe.bpe_number
    WHERE (wbpe.bpe_payment_frequency = 'O'
    AND bpe.bpe_payment_frequency <> 'O'
    AND bps.bpe_schedule_status = 'U'
    AND wbpe.xoption IN (adv_declarations.c_modify));

I recommend running the SELECT * first to make sure it pulls back the stuff you want before deleting. :)  After you verify, you can just change SELECT * to DELETE and you're good to go.
0
 
yuchingCommented:
try this
Delete From batch_payment_schedule bps
Where Exists (
Select 1 From w_batch_pledge_entry wbpe 
 INNER JOIN batch_pledge_entry bpe ON bpe.bpe_batch_number = wbpe.bpe_batch_number
    AND wbpe.bpe_number = bpe.bpe_number
WHERE (wbpe.bpe_payment_frequency = 'O' 
    AND bpe.bpe_payment_frequency <> 'O'
    AND bps.bpe_schedule_status = 'U' AND wbpe.xoption IN (adv_declarations.c_modify)
	And wbpe.bpe_batch_number = bps.batch_number
    And wbpe.bpe_number = bps.bpe_number
	)

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now