CODEWITHSUNDEEP
phpmysql
user2431709
user2431709

Reputation:

PHP Delete rows from MySQL where two tables don't match

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

Upvotes: 0

Views: 107

Answers (3)

Joe Taras
Joe Taras

Reputation: 15389

Try this:

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

Upvotes: -1

mirkobrankovic
mirkobrankovic

Reputation: 2347

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

Upvotes: 3

xlecoustillier
xlecoustillier

Reputation: 16351

DELETE FROM tickets_updates
WHERE  ticket_seq NOT IN (SELECT sequence
                           FROM   tickets)

Upvotes: 5

Related Questions