SQL for delete query

Question

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.

Is this query wrong, and if it is (which probably is), how can I write such a query?

Answer 1

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 2

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.