Re: sql to delete rows from joining a table to itself
Date: Wed, 01 Jul 2009 07:30:34 +0200
Message-ID: <4a4af47a$0$199$e4fe514c_at_news.xs4all.nl>
Peter Nilsson schreef:
> 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?! :-)
No, not in here, but in my other post. I noticed the deletion of too many rows in your query, and later found out that the original query did the same, and luckily did not work!
>
> 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. ;-)
Right, especially when there is no space for backups!
>
> --
> Peter
Shakespeare Received on Wed Jul 01 2009 - 00:30:34 CDT