Friday, December 19, 2008

Someone Has a Sense of Humor

Found this in Microsoft's sql.h file - line 535:

/* SQL_OJ_CAPABILITIES bitmasks */    /* NB: this means 'outer join', not what you may be thinking */

Monday, December 1, 2008

DELETE FROM FROM ?

I ran across this today:

DELETE Customer
  FROM Customer, Invoice
 WHERE Customer.CustomerIndex = Invoice.CustomerIndex

Despite how odd it looks at first glance, it is legal SQL (at least, extended T-SQL). The oddness is because the DELETE statement can have two optional FROM clauses: in the code above, we haven't used the first one, but have used the second.

The first FROM clause "is an optional keyword that can be used between the DELETE keyword and the target table_name..." (BOL). The second FROM allows you to specify data from another, JOINed table, and delete corresponding rows from the table in the first FROM clause.

Personally I think writing this statement as:

DELETE FROM Customer
  FROM Invoice
 WHERE Customer.CustomerIndex = Invoice.CustomerIndex

... would probably be a lot clearer.