Re: sql to delete rows from joining a table to itself

From: Peter Nilsson <airia_at_acay.com.au>
Date: Tue, 30 Jun 2009 16:45:10 -0700 (PDT)
Message-ID: <fa6e1e6c-4d61-4e9f-b6e3-190d1595f2e1_at_c9g2000yqm.googlegroups.com>

Shakespeare <what..._at_xs4all.nl> wrote:
> Peter Nilsson schreef:
> > ...
> > Note that if there are rows with the same (id, date,
> > flag = 'Q', dstop) tuple, but different batch numbers,
> > then you'll get every row for that tuple.
> >
> > Is that really what you want?...

<snip>
> > Hint: A better select query might be...
> > select a.*
> > from trafficdata a
> > where a.flag = 'Q'
> > and exists
> > (select 1
> > from trafficdata b
> > where b.id = a.id
> > and b.date = a.date
> > and b.flag = a.flag
> > and b.dstop = a.dstop
> > and ( b.batch != a.batch
> > or b.batch = a.batch
> > and b.pos_marker > a.pos_marker));

>

> I took a quick look, but if there are corresponding rows
> on (id, date, flag, dstop) where a.batch <> b.batch it
> looks like BOTH rows will be deleted. I don't know if
> that is what you really wanted...

Is there an echo in here?! :-)

The query I posted replicates the semantics of the OP's original select, except that it doesn't repeat matching rows from the target table.

The hint about my script maybe being better was that it's a one line change to make it delete instead of select rows.

That sort of select is useful in situations like formal data change requests where you have to confirm the affected rows with pre and post selects. Performing a review of the change script prior to running in production is much easier if the select and delete (or update) are all but identical.

Of course, it's important to get the select right in the first place. ;-)

--
Peter

Received on Tue Jun 30 2009 - 18:45:10 CDT