SQL for delete query

Question

Hi,

I'm trying to write an SQL query for my program, but I just can't figure out how. I don't know enough SQL.

I'm trying to implement an online team system (for some website). I have two tables:

teams | teamId, eventId
teammembers | teamId, userId, status

Now, I need to: "delete all records in teammembers where the eventId for the corresponding teamId is 1.

I'm trying:

delete from teammembers where teamId=teams.teamId and teams.eventId=1;

I'm not sure if this is really doing what I'm trying to do.

Please tell me if my query is wrong, and if it is (which probably is), please teach me how to write such a query.

Thanks!

Answer 1

delete from teammembers where teamid in 
(
  select teamid from teams where eventid = 1
)

Assuming that teamid is the joining column.

Answer 2

You don't specify your RDBMS so here it is in MySQL

DELETE teammembers FROM teammembers
JOIN teams on teammembers.teamId = teams.teamId
WHERE teams.eventId = 1

Answer 3

You need to do a sub-query or a join, I think join is faster.

delete from teammembers
join teams on teammembers.teamid = teams.teamid
where teams.eventid = 1

Answer 4

This is what you want:

delete from teammembers where teamId in (select teamId from teams where eventId=1);

Edit: As some comments already suggest, the more efficient way is indeed through a join:

delete from teammebers
join teams on teams.teamId = teammembers.teamId
where eventId = 1

Answer 5

You need to use the multi-table delete syntax:

DELETE teammembers
FROM teammembers
JOIN teams ON teamId=teams.teamId 
WHERE teams.eventId=1;

(I'm assuming MySQL here)

Answer 6

This is what I would do in SQL Server

DELETE tm 
--select tm.*
FROM teammembers tm
JOIN teams t on tm.teamId = t.teamId 
WHERE t.eventId = 1 

By embedding a select in the comment, I can run just this part manually and see what records I'm going to affect before I run the delete while I'm in development.

select tm.*
FROM teammembers tm
JOIN teams t on tm.teamId = t.teamId 
WHERE t.eventId = 1 

I never run a delete statment for the first time without checking to see that the records I think I'm going to delete are the records I intended to delete. This check will save you lots of worry as to whether your more complex deletes are affecting only the records you want to affect.