Re: SQL advice please
From: "Steve Barnes" <sgbarnes99@hotmail.com>
>
>Morning all and Obnoxio,
At least it wasn't "Morning ladies and gentlemen 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?
Not offhand, no. But I'm feeling lazy today. :-)
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com
How about
Delete from table_a where exists (select * from table_b where
table_b.col_a=table_a.col_a and table_b.col_b=table_a.col_b)
Your query would delete a row incorrectly if there were a row in table_a
with a value of "AB" in col_a and a blank col_b, while table_b contained "A"
and "B" in col_a and col_b, respectively.
HTH,
Doug
"Obnoxio The Clown" <obnoxio@hotmail.com> wrote in message
news:89gkfm$t52$1@news.xmission.com...
>
> From: "Steve Barnes" <sgbarnes99@hotmail.com>
> >
> >Morning all and Obnoxio,
>
> At least it wasn't "Morning ladies and gentlemen 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?
>
> Not offhand, no. But I'm feeling lazy today. :-)
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
>