0

I have two tables:

tickets
tickets_updates

I think there are a few extra rows in tickets_updates where a record doesn't exist in the tickets table

What is the best way to run SQL Code to say:

Delete all from tickets_updates where ticket_seq is not in tickets (sequence)

sequence in the tickets table matches ticket_seq in the tickets_updates table

Improve this question

3 Answers 3

Reset to default
5 
DELETE FROM tickets_updates
WHERE  ticket_seq NOT IN (SELECT sequence
                           FROM   tickets)
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

3 
DELETE FROM tickets_updates tu
LEFT JOIN tickets t ON tu.ticket_seq = t.sequence
WHERE t.sequence IS NULL
Improve this answer

Comments

-1

Try this:

DELETE FROM ticket_updates
WHERE NOT EXISTS
    (SELECT 'x' from tickets
    WHERE tickets.sequence = ticket_updates.ticket_seq)
Improve this answer

8 Comments

not exist is expensive in mysql. A query that cheap in writing could be expensive with execution.
Yes but NOT IN you must pay attention if set is null and another issue is about long elements list. And using on LEFT JOIN is not SQL Standard
To be fair, it's not that expensive.
@Strawberry. I think too that
Hm, I'm also pretty sure that LEFT [OUTER] JOIN is standard - and it is the method I'd adopt
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.