Please explain using ON with delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Please explain using ON with delete

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2003-07-15 : 18:56:05

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'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-15 : 19:01:34

The ON part is just part of the JOIN syntax, like this:

SELECT a.Column1, b.Column3, a.Column4
FROM Table1 a
INNER JOIN Table2 ON a.Column1 = b.Column1
WHERE b.Column2 <> 0

which is the same as

SELECT a.Column1, b.Column3, a.Column4
FROM Table1 a, Table2 b
WHERE a.Column1 = b.Column2 AND b.Column2 <> 0

So yes it is like a where clause but you use it when you use the JOIN syntax instead of like the second example.

Tara

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-07-15 : 19:06:54

I should mention that the same explanation applies for the DELETE statement.

I believe this is the other way your T-SQL could be written:

delete due_evals
from due_evals d, emp_information_test e
where 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'

Tara

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2003-07-15 : 19:12:28

Quote from BOL:

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

The SQL-92 standard does support the specification of inner joins in either the FROM or WHERE clause. Inner joins specified in the WHERE clause do not have the same problems with ambiguity as the Transact-SQL outer join syntax.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources