[dba-SQLServer] DELETE and LEFT JOIN

This is one of the solutions I came up with -- but thanks for the
confirmation. 

Yesterday, I learned that when you use DELETE with any JOIN you MUST use the
second FROM clause? Anyone heard of this? 

Susan H. 

Susan,

Doing some maint and came across this which I wrote some time ago.

DELETE t_tfm_CLEARFELL
FROM t_tfm_CLEARFELL AS t1, #t AS t2
WHERE t1.Job_Number = t2.Job_Number 
	AND t1.Region = t2.Region
	AND t1.IsCompartment = t2.IsCompartment
	AND t1.Age = t2.Age
	AND t1.Compartment = t2.Compartment

There used to be a way to force outer joins in the where part.  IIRC Maybe
that will work, can't test just now.

cheers

Michael M


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Susan
Harkins
Sent: Saturday, 6 May 2006 7:35 AM
To: SQLList
Subject: [dba-SQLServer] DELETE and LEFT JOIN

I understand that DELETE won't delete records from the nullable side of a
LEFT JOIN. However, what I'm finding is that I can't use DELETE with any
LEFT JOIN -- can someone clarify this for me? 
 
Susan H. 

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.392 / Virus Database: 268.5.5/334 - Release Date: 5/8/2006