Just for reference, this was the code used, broken down into steps to allow me to sense check at each stage :- SELECT U.id,U.email,U.name,U.lastVisitDate,G.userid,G.groupid FROM mos_users AS U INNER JOIN mos_graccess_usergroup AS G ON G.userid = U.id AND G.groupid = 3 WHERE U.email IN (SELECT EmailAddress FROM PingUserDelete) LIMIT 0,99999 UPDATE mos_graccess_usergroup INNER JOIN mos_users ON mos_graccess_usergroup.userid = mos_users.id SET mos_graccess_usergroup.status = 'D' WHERE mos_users.email IN (SELECT EmailAddress FROM PingUserDelete) AND mos_graccess_usergroup.groupid = 3 DELETE FROM mos_graccess_usergroup WHERE mos_graccess_usergroup.status = 'D' Rob. -----Original Message----- From: php-objects@xxxxxxxxxxxxxxx [mailto:php-objects@xxxxxxxxxxxxxxx] On Behalf Of Atkinson, Robert Sent: 22 March 2011 09:12 To: php-objects@xxxxxxxxxxxxxxx Subject: RE: Re: Help With Cursor-Type Statement Jack thanks for your reply. I found the documentation on Cursors, at least as of 5.0 - http://dev.mysql.com/doc/refman/5.0/en/cursors.html. Too be fair, I could easily have Googled that before I posted, but I was hoping that someone would come up with something simpler with a sub-select, as you did. My MySQL skills just aren't strong enough to do it myself. Many thanks again, Robert. -----Original Message----- From: php-objects@xxxxxxxxxxxxxxx [mailto:php-objects@xxxxxxxxxxxxxxx] On Behalf Of jacklkemp Sent: 22 March 2011 06:34 To: php-objects@xxxxxxxxxxxxxxx Subject: Re: Help With Cursor-Type Statement I don't think you need a cursor, you can accomplish this with a JOIN on you delete thusly: DELETE T FROM Users AS U INNER JOIN TableX AS T ON T.UserID = U.UserID AND T.GroupID = yyy WHERE U.Email = email OR if you have a table with the Emails you want to delete DELETE T FROM Users AS U INNER JOIN TableX AS T ON T.UserID = U.UserID AND T.GroupID = yyy WHERE U.Email IN (SELECT Email FROM DeleteEmail) Cursors, last I knew, are not available in MySQL --- In php-objects@xxxxxxxxxxxxxxx, "Atkinson, Robert" <ratkinson@...> wrote: > > I'm trying to write a SQL statement in MySQL which would be achieved using > Cursors in MS SQL. > > Basically, I have a table containing a list of email addresses. For each > email address, I want to read the user table (on the email address) to get > the user ID, then read another table deleting the record where user = xxx and > groupid = yyy. > > Is it possible to do looped cursor statements like this in MySQL? > > Thanks, Rob. > > > > ***************************************************************************** ****** > Any opinions expressed in email are those of the individual and not necessarily those of the company. This email and any files transmitted with it are confidential and solely for the use of the intended recipient or entity to whom they are addressed. It may contain material protected by attorney-client privilege. If you are not the intended recipient, or a person responsible for delivering to the intended recipient, be advised that you have received this email in error and that any use is strictly prohibited. > > Random House Group + 44 (0) 20 7840 8400 > http://www.randomhouse.co.uk > http://www.booksattransworld.co.uk http://www.kidsatrandomhouse.co.uk > Generic email address - enquiries@... > > Name & Registered Office: > THE RANDOM HOUSE GROUP LIMITED > 20 VAUXHALL BRIDGE ROAD > LONDON > SW1V 2SA > Random House Group Ltd is registered in the United Kingdom with company No. 00954009, VAT number 102838980 > ***************************************************************************** ****** > ------------------------------------ Are you looking for a PHP job? Join the PHP Professionals directory Now! http://www.phpclasses.org/jobs/ Yahoo! Groups Links ***************************************************************************** ****** Any opinions expressed in email are those of the individual and not necessarily those of the company. This email and any files transmitted with it are confidential and solely for the use of the intended recipient or entity to whom they are addressed. It may contain material protected by attorney-client privilege. If you are not the intended recipient, or a person responsible for delivering to the intended recipient, be advised that you have received this email in error and that any use is strictly prohibited. Random House Group + 44 (0) 20 7840 8400 http://www.randomhouse.co.uk http://www.booksattransworld.co.uk http://www.kidsatrandomhouse.co.uk Generic email address - enquiries@xxxxxxxxxxxxxxxxx Name & Registered Office: THE RANDOM HOUSE GROUP LIMITED 20 VAUXHALL BRIDGE ROAD LONDON SW1V 2SA Random House Group Ltd is registered in the United Kingdom with company No. 00954009, VAT number 102838980 ***************************************************************************** ****** ------------------------------------ Are you looking for a PHP job? Join the PHP Professionals directory Now! http://www.phpclasses.org/jobs/ Yahoo! Groups Links *********************************************************************************** Any opinions expressed in email are those of the individual and not necessarily those of the company. This email and any files transmitted with it are confidential and solely for the use of the intended recipient or entity to whom they are addressed. It may contain material protected by attorney-client privilege. If you are not the intended recipient, or a person responsible for delivering to the intended recipient, be advised that you have received this email in error and that any use is strictly prohibited. Random House Group + 44 (0) 20 7840 8400 http://www.randomhouse.co.uk http://www.booksattransworld.co.uk http://www.kidsatrandomhouse.co.uk Generic email address - enquiries@xxxxxxxxxxxxxxxxx Name & Registered Office: THE RANDOM HOUSE GROUP LIMITED 20 VAUXHALL BRIDGE ROAD LONDON SW1V 2SA Random House Group Ltd is registered in the United Kingdom with company No. 00954009, VAT number 102838980 ***********************************************************************************
