February 18, 2003 at 5:19 pm
Any opinions on which is better:
delete table1
from table1_daily join table1
on table1_daily.majkey = table1.majkey
or
delete table1
where exists (select majkey from table1_daily)
I run the top query to reconcile data between two tables - a current table and a history/archive table.
Thanks
Edited by - notrub on 02/18/2003 5:19:15 PM
February 18, 2003 at 5:41 pm
notrib, your second query will always delete all rows from the table1 one if there is at least one record in table2. It is probably not what you need. Just use the join.
Michael
February 18, 2003 at 5:43 pm
The first is safer and generally better.
The second can give bad results and cause unexpected behavior. The problem as is is the majkey exists as a column name in both the other and sub queries. Sometimes (more often than expected) the engine will assume you mean the outer table and thus every row is compared as exists. The way to fix is qualify the column with the table name.
However better index and comparison behavior in the first overall anyway.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply