delete using inner join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

delete using inner join

Author

Topic

glenncarr
Starting Member

1 Post

Posted - 2005-02-15 : 11:30:48

I'm trying to determine the most effecient way to delete records. Specifically, I want to delete all the player_stat records for a particular game_id and stat_category_id, where...

player_stats
------------
id
game_id
player_id
stat_field_id
...

stat_fields
-----------
id
stat_category_id
...

(player_stats typically has over 1 million records.)

This is what I'm considering doing, but I don't want the INNER join to join the entire player_stats table with the stat_fields table before filtering to the specific player_id. Will SQL 2000 take care of this for me? Or, is there a better way to accomplish this?

DELETE FROM player_stats
WHERE id IN (
SELECT player_stats.id
FROM player_stats INNER JOIN
stat_fields ON
player_stats.stat_field_id = stat_fields.id
WHERE (player_stats.game_id = ?)
AND (player_stats.player_id = ?)
AND (stat_fields.stat_category_id = ?))

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 13:25:35

Your statement looks fine. It could be slightly simpler (below), however you'd have to compare the execution plans to see if there is an improvement.

This is virtualy the same as your subquery


Select	ps.*
--Delete	ps
From	Player_stats ps
JOIN	stat_fields sf
		ON ps.stat_field_id = sf.[id]
Where	ps.game_id = ?
AND	ps.player_id = ?
AND	sf.stat_category_id = ?

Be One with the Optimizer
TG

TimS
Posting Yak Master

198 Posts

Posted - 2005-02-15 : 13:53:41

TG:
How do you get delete to work with table alias?
I have to NOT use an alias on deletes.

Tim S

Select Player_stats.*
-- Delete
From Player_stats
JOIN stat_fields sf
ON Player_stats.stat_field_id = sf.[id]
Where Player_stats.game_id = ?
AND Player_stats.player_id = ?
AND sf.stat_category_id = ?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-02-15 : 14:07:42

I've always thought Sql syntax was a little confusing for Deletes in that if you include the optional "From" key word, it looks like there are 2 FROM clauses. Your statement could be:

Delete FROM ps
FROM Player_stats ps
JOIN stat_fields sf
ON ps.stat_field_id = sf.[id]
Where ps.game_id = ?
AND ps.player_id = ?
AND sf.stat_category_id = ?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources