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));
bill_homeAsked:
Who is Participating?
 
anumosesCommented:
try the second one.
0
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bill_homeAuthor Commented:
Hello anumoses, you have two solutions...which one is it?
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.