July 15, 2003 at 5:01 pm
The following statement works. On seems to work just like a where clause can someone tell me why? Is this the correct way to delete items using 2 tables? I ran it and the information is correct. Will the number of items deleted is correct.
delete due_evals
from due_evals d join emp_information_test e
on d.datetobefiled between '01/01/2003' and '06/30/2003'
and d.empid = e.empid
and e.bureau not in ('375')
and d.payloc_comp_date is null
and d.eval_type = 'Probation'
July 15, 2003 at 5:34 pm
I don't know the exact terminology, but by repeating the table name in the from clause, it creates the ability to use the abbreviation (d) in the join statement. The ON refers to the JOIN and, because it is an INNER JOIN, restricts the data similar to the WHERE clause.
Another way to write the statement would be something like:
DELETE FROM due_evals
WHERE DateToBeFiled BETWEEN '01/01/2003' AND '06/30/2003'
AND payloc_comp_date IS NULL
AND eval_type = 'Probation'
AND EXISTS ( SELECT * FROM emp_information_test e (NOLOCK) WHERE
e.empid = due_evals.empid and e.bureau <> '375' )
HOWEVER, the statement you have will be more efficient.
Guarddata-
July 16, 2003 at 4:21 pm
Could you explain why the former is more efficient? Thank you very much.
July 17, 2003 at 5:04 am
The best way to find out it to look at the query plan. You have to identify the data to delete it, so a good index plan is as important there as anywhere else. What is faster one time may not be the next if you change indexes.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply