DELETE from table based on SELECT query with INNER JOIN

Question

I want to delete some records based on records that I got from other query. Problem is that I have syntax error that I can't solve.

Here is query that I am using:

DELETE FROM `tickets` as c 
WHERE c.TICKET_ID IN ( SELECT a.TICKET_ID 
FROM `tickets` as a 
INNER JOIN `old_tickets` as b 
ON b.TICKET_ID = a.TICKET_ID);

Abhik Chakraborty · Accepted Answer · 2015-04-02 12:45:05Z

1

Why you need IN clause it could be done with just join as

delete c from tickets c
join old_tickets oc on oc.TICKET_ID = c.TICKET_ID

answered Apr 2, 2015 at 12:45

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

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

Comments

Tushar · Accepted Answer · 2015-04-02 13:16:59Z

0

You have to remove Join in your subquery as anyhow you are checking for all TICKET_ID's from ticket1 which have matches in old_tickets table;

So that your query becomes:

DELETE FROM tickets t1
 WHERE t1.TICKET_ID IN (SELECT t2.TICKET_ID
                        FROM old_tickets t2)

edited Apr 2, 2015 at 13:16

answered Apr 2, 2015 at 13:05

Tushar

3,6759 gold badges31 silver badges51 bronze badges

Comments

Kypros · Accepted Answer · 2015-04-02 14:33:42Z

0

Try this

DELETE FROM tickets as a 
INNER JOIN old_tickets as b ON b.TICKET_ID = a.TICKET_ID

edited Apr 2, 2015 at 14:33

Kypros

2,9865 gold badges23 silver badges28 bronze badges

answered Apr 2, 2015 at 12:47

Róger William Calderini

111 bronze badge

Collectives™ on Stack Overflow

DELETE from table based on SELECT query with INNER JOIN

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related