CODEWITHSUNDEEP
mysqlsql
BetaRide
BetaRide

Reputation: 16834

How to delete record found by a left join

With following query I can find the data I want to delete:

SELECT ab.id, ab.anlage_id, a.id
FROM `anlagetobedienung` AS ab
LEFT JOIN anlage_new AS a ON ab.`anlage_id` = a.id
WHERE a.id IS NULL

How do I have to write the DELETE statement that deletes the found records in the anlagetobedienung table?

Upvotes: 3

Views: 158

Answers (5)

sll
sll

Reputation: 62484

DELETE ab
FROM `anlagetobedienung` AS ab 
LEFT JOIN anlage_new AS a ON ab.`anlage_id` = a.id 
WHERE a.id IS NULL

Upvotes: 4

Benoit
Benoit

Reputation: 79165

You could simply do:

DELETE FROM anlagetobedienung ab
 WHERE NOT EXISTS (SELECT NULL
                     FROM anlage_new a
                    WHERE a.id = ab.anlage_id
                  )

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 81862

Normally I'd say:

delete anlage_new 
where id in (
    SELECT  a.id
    FROM `anlagetobedienung` AS ab
    LEFT JOIN anlage_new AS a ON ab.`anlage_id` = a.id
    WHERE a.id IS NULL
)

i.e. you delete the rows where the primary key is in the result of the select that identifies the records to delete.

But according to your query those have id = NULL which doesn't seem to make any sense.

Can you provide some example data?

After your edit, I'd say this should work:

delete `anlagetobedienung` 
where id in (
    SELECT  ab.id
    FROM `anlagetobedienung` AS ab
    LEFT JOIN anlage_new AS a ON ab.`anlage_id` = a.id
    WHERE a.id IS NULL
)

Note: the inner select has 'ab.id' instead 'a.id' which seems to be the only column in the original select that has a chance of containing something useful.

Upvotes: 0

Tom H
Tom H

Reputation: 47444

Another possible solution:

DELETE FROM anlagetobedienung A
WHERE
    NOT EXISTS (SELECT * FROM anlage_new AN WHERE AN.id = A.anlage_id)

If MySQL supports aliases in a DELETE FROM (supported in MS SQL, but I know that some of the major RDBMS's don't support this) then you could do:

DELETE ab
FROM
    anlagetobedienung AS ab
LEFT JOIN anlage_new AS a ON
    ab.anlage_id = a.id
WHERE
    a.id IS NULL

Upvotes: 0

Johan
Johan

Reputation: 76537

DELETE FROM anlagebedienung aDEL WHERE aDEL.id IN 
 (SELECT ab.id 
  FROM `anlagetobedienung` AS ab
  LEFT JOIN anlage_new AS a ON ab.`anlage_id` = a.id
  WHERE a.id IS NULL )

Upvotes: 0

Related Questions