CODEWITHSUNDEEP
mysql
jureispro
jureispro

Reputation: 1402

DELETE from table based on SELECT query with INNER JOIN

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);

Upvotes: 0

Views: 52

Answers (3)

Róger William Calderini
Róger William Calderini

Reputation: 11

Try this

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

Upvotes: 0

Tushar
Tushar

Reputation: 3643

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)

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

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

Upvotes: 1

Related Questions