SQL Delete statement using JOIN

Question

I need to DELETE rows from a MySQL table based on a JOIN.

Table 1 - players

id      gang
--------------
1       5
2       8
3       0

Table 2 - actions

id      player
----------------
1       2
2       1

I need to (pseudo code)

DELETE FROM 'actions' WHERE player=(SELECT id FROM players WHERE gang=5)

So it checks through actions table and if it finds a player that is in gang 5 it removes the entry.

Sorry if I'm not making sense

What RDBMS you are using? RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL, etc... — John Woo
– John Woo, Commented Apr 19, 2013 at 11:09

John Woo · Accepted Answer · 2013-04-19 11:08:26Z

1

Try this one,

DELETE  a
FROM    Actions a
        INNER JOIN Players b
            ON a.player = b.ID
WHERE   b.gang = 5

answered Apr 19, 2013 at 11:08

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JOINs are much faster than IN especially in MySQL.

Houari · Accepted Answer · 2013-04-19 11:12:31Z

0

DELETE FROM 'actions' WHERE player IN (SELECT id FROM players WHERE gang=5)

answered Apr 19, 2013 at 11:12

Houari

5,6513 gold badges33 silver badges57 bronze badges