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

From: Shakespeare <whatsin_at_xs4all.nl>
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

Original text of this message