[dba-SQLServer] another nudge on that DELETE, OUTER JOIN quirk

Robert L. Stewart rl_stewart at highstream.net
Wed May 17 06:30:35 CDT 2006

Here is what will work:

FROM dbo.[threestar-rightangle]
WHERE (InvntryRcncleID
IN (SELECT InvntryRcncleID
    FROM dbo.tblRaInventoryTransactions))

The statement above delete duplicate recorts out of a temp table before an
append of new records.

Robert L. Stewart
The Dyson Group International
Software for the Non-profit Enterprise
Expanding your Sphere of Knowledge

Quoting dba-sqlserver-request at databaseadvisors.com:

> I'm not sure about what is technically possible in this case, but if you
> meant your query explicitly then I'd say you hit a feature, not a problem.
> Otherwise you'd be orphaning Orders rows.
> Susan Harkins <harkinsss at bellsouth.net> wrote: I tried yours -- the only
> difference seems to be that you specified the
> non-nullable table. Using the following statement, I still got an error:
> DELETE Customers.*
> FROM Customers LEFT JOIN Orders
> ON Customers.CustID = Orders.CustID
> I'm using Express -- prehaps the two versions aren't compatiable on this
> issue?
> Susan H.

More information about the dba-SQLServer mailing list