Re: SQL advice please
Steve Barnes wrote:
>
> 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?
If you have rowids or serial ids, and you'll allow yourself two statements:
select table_a.rowid the_rowid
from table_a, table_b
where table_a.col_a = table_b.col_a
and table_a.col_b = table_b.col_b
into temp the_rowids with no log;
DELETE FROM table_a
WHERE rowid IN (
SELECT the_rowid
from the_rowids
);
What about nulls in those fields?
Also, too many deletes at once can fill your logical logs.
--
Colin