RE: SQL advice please
This is to be achieved using SQL only.
Table_a contains about 350,000 rows, table_b probably about 20,000.
Thanks to all the suggestions so far. I know the original method is far from
perfect, but does actually work as col_a is char containing only numbers,
and col_b is a char containing only characters. Please don't ask me why it
is this way, it is an inherited layout!
Steve
>From: William Rice <ricew@operamail.com>
>To: Steve Barnes <sgbarnes99@hotmail.com>
>CC: informix-list <informix-list@iiug.org>
>Subject: RE: SQL advice please
>Date: Tue, 29 Feb 2000 12:21:34 -0500
>
>Is this a program, or ad hoc sql in dbaccess?
>
>How many rows do you expect to delete?
>How many rows are in table_a and table_b respectively.
>
>Will
>
> >> From: Steve Barnes [SMTP:sgbarnes99@hotmail.com]
> >> Sent: Tuesday, February 29, 2000 7:25 AM
> >> To: informix-list@iiug.org
> >> Subject: SQL advice please
> >>
> >> Morning all and Obnoxio,
> >>
> >> I am trying to delete some rows from a table where the same columns
>exist
> >> in
> >> another table, but there are two columns to check. I want to delete all
> >> rows
> >> in table_a where col_a and col_b are in table_b.
> >>
> >> The only way I have so far is as follows
> >>
> >> DELETE FROM table_a WHERE trim(col_a)||trim(col_b) IN
> >> (SELECT trim(col_a)||trim(col_b) from table_b)> >>
> >> Does anyone know of a better way?
> >>
> >> Thanks
> >>
> >> Steve
> >>
> >> ______________________________________________________
> >> Get Your Private, Free Email at http://www.hotmail.com
>
>------------------------------------------------------------
>This e-mail has been sent to you courtesy of OperaMail, as a free
>service from
>Opera Software, makers of the award-winning Web Browser, Opera. Visit us
>at
>http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free
>e-mail
>account is waiting at: http://www.operamail.com/
>------------------------------------------------------------
>
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
I'd still put in a delimiter character. Otherwise 10|"0123" could match
10012|"3". Maybe col1 || "|" || col2....
Steve Barnes wrote:
> This is to be achieved using SQL only.
>
> Table_a contains about 350,000 rows, table_b probably about 20,000.
>
> Thanks to all the suggestions so far. I know the original method is far from
> perfect, but does actually work as col_a is char containing only numbers,
> and col_b is a char containing only characters. Please don't ask me why it
> is this way, it is an inherited layout!
>
> Steve
>
> >From: William Rice <ricew@operamail.com>
> >To: Steve Barnes <sgbarnes99@hotmail.com>
> >CC: informix-list <informix-list@iiug.org>
> >Subject: RE: SQL advice please
> >Date: Tue, 29 Feb 2000 12:21:34 -0500
> >
> >Is this a program, or ad hoc sql in dbaccess?
> >
> >How many rows do you expect to delete?
> >How many rows are in table_a and table_b respectively.
> >
> >Will
> >
> > >> From: Steve Barnes [SMTP:sgbarnes99@hotmail.com]
> > >> Sent: Tuesday, February 29, 2000 7:25 AM
> > >> To: informix-list@iiug.org
> > >> Subject: SQL advice please
> > >>
> > >> Morning all and Obnoxio,
> > >>
> > >> I am trying to delete some rows from a table where the same columns
> >exist
> > >> in
> > >> another table, but there are two columns to check. I want to delete all
> > >> rows
> > >> in table_a where col_a and col_b are in table_b.
> > >>
> > >> The only way I have so far is as follows
> > >>
> > >> DELETE FROM table_a WHERE trim(col_a)||trim(col_b) IN
> > >> (SELECT trim(col_a)||trim(col_b) from table_b)> > >>
> > >> Does anyone know of a better way?
> > >>
> > >> Thanks
> > >>
> > >> Steve
> > >>
> > >> ______________________________________________________
> > >> Get Your Private, Free Email at http://www.hotmail.com
> >
> >------------------------------------------------------------
> >This e-mail has been sent to you courtesy of OperaMail, as a free
> >service from
> >Opera Software, makers of the award-winning Web Browser, Opera. Visit us
> >at
> >http://www.opera.com/ or our portal at: http://www.myopera.com/ Your free
> >e-mail
> >account is waiting at: http://www.operamail.com/
> >------------------------------------------------------------
> >
>
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
--
Madison Pruet
===========================================
Enterprise Replication Product Developement
Dallas, Texas
Informix Software
===========================================
Steve Barnes wrote:
> This is to be achieved using SQL only.
>
> Table_a contains about 350,000 rows, table_b probably about 20,000.
>
> Thanks to all the suggestions so far. I know the original method is far from
> perfect, but does actually work as col_a is char containing only numbers,
> and col_b is a char containing only characters. Please don't ask me why it
> is this way, it is an inherited layout!
>
As Doug had suggested earlier, this requirement begs for the use of the EXISTS
clause
DELETE FROM table_a
WHERE EXISTS (
SELECT 1 FROM table_b
WHERE table_a.col_a = table_b.col_a
AND table_a.col_b = table_b.col_b);
Avoid using the TRIM function in the WHERE clause unless it is a business
requirement.
An index on table_b (col_a, col_b) would help, unless the TRIM function is
used. If reasonably unique indexes on table_b.col_a or table_b.col_b already
exist, the col_a, col_b would not be essential.
Rudy