CODEWITHSUNDEEP
mysqlsqlsql-delete
Bramat
Bramat

Reputation: 997

Delete records from table according to another table

I have two tables in MYSQL that looks something like this:

Table A:

id1  |  id2  |  id3  |  id4  |
 1   |   15  |   26  |   12  |
 2   |   27  |   12  |   15  |
 1   |   4   |   82  |   18  |
 4   |   11  |   37  |   22  |
 1   |   21  |   31  |   24  |

Table b:

id1  |  id2  |
 2   |   27  | 
 1   |   21  |

I want to delete the rows in table A that have the partial match of fields in Table B (meaning delete rows 2 and 5 from table A).

does anyone have an idea? Thank You :)

Upvotes: 2

Views: 53

Answers (2)

Ike Walker
Ike Walker

Reputation: 65547

You can use the multi-table delete syntax:

delete TableA.* 
from TableA
inner join TableB on TableB.id1 = TableA.id1 and TableB.id2 = TableA.id2;

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Use Exists or Join syntax to delete the matched records. Try this

delete 
from TableA
where exists 
(
select 1 
from TableB B 
where TableA.id1 = B.id1 and TableA.id2 = B.id2
)

Upvotes: 1

Related Questions