CODEWITHSUNDEEP
mysqlsql-deleterecords
Raymosrunerx
Raymosrunerx

Reputation: 179

Deleting records in MySQL where IDs from two tables match

I have two tables, TableA and TableB, and each has a field called ID.

I want to delete all the records in TableA where the ID matches a record in TableB.

I try to do this command, but TableB is not found

DELETE FROM TableA WHERE TableA.id=TableB.id;

Upvotes: 1

Views: 3596

Answers (4)

Jason Hoekstra
Jason Hoekstra

Reputation: 162

How about using an INNER JOIN? This worked for me in MySQL:

DELETE tablea FROM tablea
INNER JOIN tableb
ON tablea.ID = tableb.ID

Upvotes: 1

Mike Purcell
Mike Purcell

Reputation: 19979

You may want to consider using foreign key constraints so you don't have to manually do these types of operations, rather the database will handle them for you.

For example, if you had a shared id between tableA and tableB, and a delete from tableA occurred, MySQL would handle the delete from tableB for you automatically, and you wouldn't have to worry about joining to other tables that had a FK constraint to tableA.

It could be as simple as:

DELETE FROM tableA WHERE id = some_id LIMIT 1;

Upvotes: 2

bfavaretto
bfavaretto

Reputation: 71918

You can't reference TableB on WHERE because that table was not joined. But on this case you don't even need a JOIN, use this:

DELETE FROM TableA WHERE id IN (SELECT id FROM TableB)

Upvotes: 4

Frederick Cheung
Frederick Cheung

Reputation: 84114

I would use a join

DELETE TABLEA FROM TABLEA INNER JOIN TABLEB USING(ID)

Big subqueries can be slow if MySQL ends up having to actually materialise the temporary table - definitely worth checking the query plan

Upvotes: 1

Related Questions