delete from table where multiple columns match
User needed to delete rows from table newrt matching multiple columns in hold_newrt. Solutions offered included a correlated subquery with EXISTS (poster's approach) and a legacy "1 IN" technique. Art Kagel suggested MERGE statement. However, poster tested the "1 IN" approach with SELECT and found it incorrectly matched all 83M rows instead of expected 1.6M, realizing it acts as self-join. No resolution was safely tested due to long query times.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
WOW! That is one unclear subject line! Sorry about that. I'm also sure this
has been posted and may even have been answered but I have yet to find an
answer; it's so difficult to compose the perfect search phrase.
IDS 11.5
I have carefully created a work table named hold_newrt. Its primary key is
comprised of 2 columns, say (pkcol1, pkcol2). From another table, newrt, I
with to delete all rows whose corresponding columns match those two in
hold_newrt.
Logically, I would like to use SQL something like this:
delete from newrt n
where n.pkcol1 = hold_newrt.pkcol1
and n.pkcol2 = hold_newrt.pkcol2
Right now I'm looking at something like:
delete from newrt n
where exists
(select * from hold_newrt hn
where hn.brand = n.brand
and hn.rec_num = n.rec_num)
A correlated subquery! I'm currently running a SELECT based on a similar
subquery-WHERE clause to see what would be deleted but I'd rather avoid that
kind of subquery altogether.
Solutions, anybody?
Thanks much!
-- Jacob S
Hi Jacob,
The recommended syntax from the Informix TechNote(!) in read back in late 90's
was:
delete from a
where 1 in (select 1 from a,b where a.id=b.id)
FYI Not related but still a useful site:
http://www-947.ibm.com/support/entry/portal/Problem_resolution/Software/Informat
ion_Management/Informix_Servers
Regards,
David.
> On 09 August 2016 at 21:45 JACOB SALOMON <jakesalomon@yahoo.com> wrote:
>
>
> WOW! That is one unclear subject line! Sorry about that. I'm also sure this
> has been posted and may even have been answered but I have yet to find an
> answer; it's so difficult to compose the perfect search phrase.
>
> IDS 11.5
>
> I have carefully created a work table named hold_newrt. Its primary key is
> comprised of 2 columns, say (pkcol1, pkcol2). From another table, newrt, I
> with to delete all rows whose corresponding columns match those two in
> hold_newrt.
>
> Logically, I would like to use SQL something like this:
>
> delete from newrt n
> where n.pkcol1 = hold_newrt.pkcol1>
> and n.pkcol2 = hold_newrt.pkcol2
>
> Right now I'm looking at something like:
>
> delete from newrt n
> where exists
> (select * from hold_newrt hn>
> where hn.brand = n.brand
>
> and hn.rec_num = n.rec_num)
>
> A correlated subquery! I'm currently running a SELECT based on a similar
> subquery-WHERE clause to see what would be deleted but I'd rather avoid that
> kind of subquery altogether.
>
> Solutions, anybody?
>
> Thanks much!
>
> -- Jacob S
>
>
>
*******************************************************************************
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Jacob:
You can use MERGE for this:
MERGE INTO newrt AS n
USING hold_newrt AS hn
ON n.brand = hn.brand AND n.rec_num = hn.rec_num
WHEN MATCHED THEN DELETE
;
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on the IIUG, nor any other organization with which I am
associated either explicitly, implicitly, or by inference. Neither do
those opinions reflect those of other individuals affiliated with any
entity with which I am affiliated nor those of the entities themselves.
On Tue, Aug 9, 2016 at 5:34 PM, david@smooth1.co.uk <david@smooth1.co.uk>
wrote:
> Hi Jacob,
>
> The recommended syntax from the Informix TechNote(!) in read back in late
> 90's
> was:
>
> delete from a
> where 1 in (select 1 from a,b where a.id=b.id)>
> FYI Not related but still a useful site:
>
> http://www-947.ibm.com/support/entry/portal/Problem_resolution/Software/
> Information_Management/Informix_Servers
>
> Regards,
> David.
>
> > On 09 August 2016 at 21:45 JACOB SALOMON <jakesalomon@yahoo.com> wrote:
> >
> >
> > WOW! That is one unclear subject line! Sorry about that. I'm also sure
> this
> > has been posted and may even have been answered but I have yet to find an
> > answer; it's so difficult to compose the perfect search phrase.
> >
> > IDS 11.5
> >
> > I have carefully created a work table named hold_newrt. Its primary key
> is
> > comprised of 2 columns, say (pkcol1, pkcol2). From another table, newrt,
> I
> > with to delete all rows whose corresponding columns match those two in
> > hold_newrt.
> >
> > Logically, I would like to use SQL something like this:
> >
> > delete from newrt n
> > where n.pkcol1 = hold_newrt.pkcol1> >
> > and n.pkcol2 = hold_newrt.pkcol2
> >
> > Right now I'm looking at something like:
> >
> > delete from newrt n
> > where exists
> > (select * from hold_newrt hn> >
> > where hn.brand = n.brand
> >
> > and hn.rec_num = n.rec_num)
> >
> > A correlated subquery! I'm currently running a SELECT based on a similar
> > subquery-WHERE clause to see what would be deleted but I'd rather avoid
> that
> > kind of subquery altogether.
> >
> > Solutions, anybody?
> >
> > Thanks much!
> >
> > -- Jacob S
> >
> >
> >
> ************************************************************
> *******************
> >
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
>
>
> ************************************************************
> *******************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a1140b16085bf6f0539ab43b3
Thanks Dave and Art. First: Dave, before I would run your WHERE clause with a delete, I tied a select, with an unload. If it were correct, the rows returned would have numbered exactly 1.6 million. It actually unloaded the entire blessed table of ~ 83 million rows. And it makes sense, in 20/20 hindsight: It is a logical equivalent of a self join and EVERY row matches itself in there. We would still need a rowid to distinguish the rows. I would experiment more but each query takes hours to run! Art: I don't see how I can test this safely. So i'll let this go for now. I will take another tack this time, if I can find it quickly enough. Lesson learned: ALWAYS create a fragmented table WITH ROWIDS. Thanks much, guys! -- Jacob S.
Better answer would be to always have a primary key on your table. If you ever decided to use ER that certainly used to require a primary key. Regards, David. > On 12 August 2016 at 04:58 JACOB SALOMON <jakesalomon@yahoo.com> wrote: > > > Thanks Dave and Art. > > First: Dave, before I would run your WHERE clause with a delete, I tied a > select, with an unload. If it were correct, the rows returned would have > numbered exactly 1.6 million. It actually unloaded the entire blessed table of > > ~ 83 million rows. And it makes sense, in 20/20 hindsight: It is a logical > equivalent of a self join and EVERY row matches itself in there. We would > still need a rowid to distinguish the rows. > > I would experiment more but each query takes hours to run! > > Art: I don't see how I can test this safely. So i'll let this go for now. > > I will take another tack this time, if I can find it quickly enough. > > Lesson learned: ALWAYS create a fragmented table WITH ROWIDS. > > Thanks much, guys! > > -- Jacob S. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. >
David, Latter versions don't, can't remember when it happened. You can add ERKEYS to the table and they will fake a PK (couple of limitations on the earlier versions) and AFAIR the latest ER only require a unique index Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of david@smooth1.co.uk Sent: Friday, August 12, 2016 8:23 AM To: ids@iiug.org Subject: Re: delete from table where multiple columns match [37605] Better answer would be to always have a primary key on your table. If you ever decided to use ER that certainly used to require a primary key. Regards, David. > On 12 August 2016 at 04:58 JACOB SALOMON <jakesalomon@yahoo.com> wrote: > > > Thanks Dave and Art. > > First: Dave, before I would run your WHERE clause with a delete, I tied a > select, with an unload. If it were correct, the rows returned would have > numbered exactly 1.6 million. It actually unloaded the entire blessed table of > > ~ 83 million rows. And it makes sense, in 20/20 hindsight: It is a logical > equivalent of a self join and EVERY row matches itself in there. We would > still need a rowid to distinguish the rows. > > I would experiment more but each query takes hours to run! > > Art: I don't see how I can test this safely. So i'll let this go for now. > > I will take another tack this time, if I can find it quickly enough. > > Lesson learned: ALWAYS create a fragmented table WITH ROWIDS. > > Thanks much, guys! > > -- Jacob S. > > > **************************************************************************** *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.