CODEWITHSUNDEEP
mysqlsqlsql-delete
jrharshath
jrharshath

Reputation: 26583

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?

Upvotes: 2

Views: 667

Answers (6)

rmn
rmn

Reputation: 2426

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

Upvotes: 0

HLGEM
HLGEM

Reputation: 96650

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.

Upvotes: 6

Hogan
Hogan

Reputation: 70528

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

Upvotes: 4

Mark Byers
Mark Byers

Reputation: 839214

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)

Upvotes: 3

hobodave
hobodave

Reputation: 29301

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

Upvotes: 12

davek
davek

Reputation: 22925

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

Assuming that teamid is the joining column.

Upvotes: 1

Related Questions