delete from Payments where UserID in (select UserID from Users where UserName = 'John Smith');
will get the job done. And I'm sure there's a more elegant method. -Clark ----- Original Message ---- From: Scott Baker <[EMAIL PROTECTED]> To: SQLITE <[email protected]> Sent: Thursday, July 12, 2007 3:47:37 PM Subject: [sqlite] DELETE using a join? Let's say I have two really simple tables like so Users ----- UserName UserID Payments -------- UserID PaymentAmount Is there an easy way to do something like "delete all entries in the payment table where the Username is 'John Smith'" I'm thinking it would be trivial to do it with a subquery, but is there a way to do it with an inner join (wouldn't that be faster). Something like (it doesn't work): DELETE FROM Payments INNER JOIN Users USING (UserID) WHERE UserName = 'John Smith'; -- Scott Baker - Canby Telcom RHCE - System Administrator - 503.266.8253 ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
