SQL for delete query

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?

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

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.

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

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)

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

Assuming that teamid is the joining column.

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