I've got a scenario like this:CREATE TABLE users (i int identity, login varchar(20), last_login smalldatetime)CREATE TABLE user_records (i_users int, d1 int, d2 in2)
Now, if I want to delete all of the user_records for users who haven't logged in in the past 30 days, what's the best way to do this? Selecting the records is easy:select * from user_records join users on i_users=users.i where last_login<=dateadd(day,-30,getdate())
...But that's not going to work for a delete. So far, I've come up with:delete from user_records where i_users in (select i from users where last_login<=dateadd(day,-30,getdate())
...But that seems kind of ugly. The other alternative I see is:delete from user_records where (select last_login from users where users.i=user_records.i_users)<dateadd(day,-30,getdate())
...But that seems even worse (one select per user_record). Anyone have any thoughts on this? I feel like I'm missing something fundamental. What I want is something like:delete from user_records join users on user_records.i_users=users.i where last_login<=dateadd(day,-30,getdate())
...But that doesn't seem to exist, syntactically.Thanks-b 
Deleting from one table in a join?
Posted - 2002-12-10 : 13:16:51
