how to write delete statement with inner join and subquery

Question

whats wrong in below query, its throwing error SQL Error: ORA-00933: SQL command not properly ended 00933. 00000 - "SQL command not properly ended". Want to delete all payment_ids from two tables which are matching with ERROR_LOG err_payment_id Please help me in correcting query.

TBLPAYMENT(PAYMENT_ID, OTHER COLUMNS)
TBLPAYMENT_ALT_ID (PAYMENT_ID, OTHER COLUMNS)
ERROR_LOG(ERR_PAYMENT_ID, OTHER COLUMNS)

    DELETE TBLPAYMENT, TBLPAYMENT_ALT_ID FROM TBLPAYMENT PYMT INNER JOIN 
    TBLPAYMENT_ALT_ID PAI ON PYMT.PAYMENT_ID = PAI.PAYMENT_ID 
    WHERE PYMT.PAYMENT_ID IN (SELECT ERR_PAYMENT_ID FROM ERROR_LOG);

Where in the Oracle Manual did you find the syntax that supports INNER JOIN in the DELETE statement? — user330315
– user330315, Commented Feb 19, 2018 at 12:05

Kaushik Nayak · Accepted Answer · 2018-02-19 12:05:36Z

2

You need 2 delete statements in Oracle.

DELETE TBLPAYMENT PYMT WHERE PYMT.PAYMENT_ID IN (SELECT ERR_PAYMENT_ID FROM ERROR_LOG);

DELETE TBLPAYMENT_ALT_ID PAI WHERE PAI.PAYMENT_ID IN (SELECT ERR_PAYMENT_ID FROM ERROR_LOG);

answered Feb 19, 2018 at 12:05

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

Jayasurya Satheesh · Accepted Answer · 2018-02-19 12:05:44Z

0

You Can't delete using Join in SQL Server. Also, you can't delete from multiple tables at a time using the normal Delete. Instead, Make it 2 delete statements like this :

DELETE FROM TBLPAYMENT
       WHERE EXISTS
       (      
          SELECT 1 FROM ERROR_LOG WHERE ERR_PAYMENT_ID = TBLPAYMENT.PAYMENT_ID
       )
DELETE FROM TBLPAYMENT_ALT_ID
       WHERE EXISTS
       (      
          SELECT 1 FROM ERROR_LOG WHERE ERR_PAYMENT_ID = TBLPAYMENT_ALT_ID.PAYMENT_ID
       )

answered Feb 19, 2018 at 12:05

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

1 Comment

Gordon Linoff Over a year ago

You can delete with a join in SQL Server, but not Oracle.

NiKhil Kutewalla · Accepted Answer · 2018-02-19 12:53:36Z

0

Try this -

DELETE from TBLPAYMENT where PAYMENT_ID in (select PYMT.PAYMENT_ID FROM TBLPAYMENT PYMT INNER JOIN 
TBLPAYMENT_ALT_ID PAI ON PYMT.PAYMENT_ID = PAI.PAYMENT_ID 
WHERE PYMT.PAYMENT_ID IN (SELECT ERR_PAYMENT_ID FROM ERROR_LOG))

DELETE from TBLPAYMENT_ALT_ID where PAYMENT_ID in (select PAI.PAYMENT_ID FROM TBLPAYMENT PYMT INNER JOIN 
TBLPAYMENT_ALT_ID PAI ON PYMT.PAYMENT_ID = PAI.PAYMENT_ID 
WHERE PYMT.PAYMENT_ID IN (SELECT ERR_PAYMENT_ID FROM ERROR_LOG))

answered Feb 19, 2018 at 12:53

NiKhil Kutewalla

1037 bronze badges

Collectives™ on Stack Overflow

how to write delete statement with inner join and subquery

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related