Create an extra trigger on that table with delete event.Then the deleted item will be recorded in the database.
On Fri, Oct 10, 2008 at 11:34 PM, Jerry Schwartz <[EMAIL PROTECTED] > wrote: > Thanks. > > > > Although I've been around SQL for quite a while, I've never really gotten > the hang of self-joins. > > > > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 08, 2008 8:22 PM > To: US Data Export; mysql@lists.mysql.com > Subject: Re: Finding gaps > > > > Jerry, > > Here is a workaround for 4.1.22: > > SELECT > a.id+1 AS 'Missing From', > MIN(b.id) - 1 AS 'To' > FROM tbl AS a, tbl AS b > WHERE a.id < b.id > GROUP BY a.id > HAVING `Missing From` < MIN(b.id); > +--------------+------+ > | Missing From | To | > +--------------+------+ > | 3 | 3 | > | 5 | 17 | > +--------------+------+ > > PB > > US Data Export wrote: > > Well, 5.x accepted the query. It's been running for awhile, now, so I'll > find out later if it did what I need. > > > > -----Original Message----- > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 08, 2008 5:25 PM > To: Jerry Schwartz; mysql@lists.mysql.com > Subject: Re: Finding gaps > > > > I must be missing something obvious; or does this not work in 4.1.22? > > > Looks like a 4.1.22 bug. > > PB > > Jerry Schwartz wrote: > > > I'm finally getting back to this issue, and I've read the bits on > artfulsoftware. The example > > SELECT > a.id+1 AS 'Missing From', > MIN(b.id) - 1 AS 'To' > FROM tbl AS a, tbl AS b > WHERE a.id < b.id > GROUP BY a.id > HAVING a.id < MIN(b.id) - 1; > > Looks like exactly what I want. However, when I try it (prod is my > > > tbl, > > > prod_num is my id) I get > > mysql> select a.prod_num + 1 AS `Missing From`, > -> MIN(b.prod_num - 1) AS `To` > -> from prod as a, prod as b > -> where a.prod_num < b.prod_num > -> group by a.prod_num > -> having a.prod_num < min(b.prod_num) -1 ; > ERROR 1054 (42S22): Unknown column 'a.prod_num' in 'having clause' > > I must be missing something obvious; or does this not work in 4.1.22? > > Regards, > > Jerry Schwartz > The Infoshop by Global Information Incorporated > 195 Farmington Ave. > Farmington, CT 06032 > > 860.674.8796 / FAX: 860.674.8341 > > www.the-infoshop.com > www.giiexpress.com > www.etudes-marche.com > > > > > -----Original Message----- > From: Peter Brawley [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 17, 2008 5:26 PM > To: Stut; mysql@lists.mysql.com > Subject: Re: Finding gaps > > > > > Is there any elegant way of finding the gaps? > > > > You'll find some ideas under (and near) "Find missing numbers in a > sequence" at http://www.artfulsoftware.com/infotree/queries.php. > > PB > > ----- > > Stut wrote: > > > > On 17 Sep 2008, at 22:12, Jerry Schwartz wrote: > > > > I have records that should be sequentially (not auto-increment) > numbered, > but there are gaps. Is there any elegant way of finding the gaps? > > > > Why do they need to be sequential? When this requirement comes up > > > it's > > > usually for illogical reasons. > > -Stut > > -------------------------------------------------------------------- > > > -- > > > -- > > > > No virus found in this incoming message. > Checked by AVG - http://www.avg.com > Version: 8.0.169 / Virus Database: 270.6.21/1677 - Release Date: > > > > 9/17/2008 5:07 PM > > > > > > ---------------------------------------------------------------------- > > > -- > > > > No virus found in this incoming message. > Checked by AVG - http://www.avg.com > Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: > > > 10/7/2008 6:40 PM > > > > > > > > > > > > > > > _____ > > > > > > No virus found in this incoming message. > Checked by AVG - http://www.avg.com > Version: 8.0.173 / Virus Database: 270.7.6/1713 - Release Date: 10/7/2008 > 6:40 PM > > > -- I'm a MySQL DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
