Very Computer

I do not understand how/why the second FROM stmt. is used.  Any ideas??

Russell

the 1st from is part of the syntax saying which table to delete from
the 2nd from is used to "join" the tables together
--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group)
-----------------------------------------------------------------

Quote:> DELETE
>  FROM Table1
>  FROM Table1, Table2
>  WHERE
>   Record_Source_Date < DATEADD(day, -90, getdate())

> I do not understand how/why the second FROM stmt. is used.  Any ideas??

> Russell

Quote:>>  DELETE FROM Table1

 I do not understand how/why the second FROM stmt. is used. <<

This is a piece of old Sybase *still left in SQL Server.
Logically, it makes no sense in the Standard SQL-92 model.  But by ex
[posing the underlying join mechanism, Sybase found they could do this
sort of thing.  They take the FROM ..WHERE.. and bvuild a query result
set, then use that to go to the table in the UPDATE clause and change
its rows.  It works because of the way that Sybase maerializes result
sets.

The basic problem is that the diseased mutant T-SQL syntax treats an
UPDATE  or DELETE FROM as just another query. It builds a working table
then uses it to go to the one base table involved thru that working
table.

In older versions of Sybase, if a base table row is represented more
than once in the hidden query, then that row is operated on multiple
times instead of just once. A total violation of relational
principles.  Here is a quick example:

 CREATE TABLE T1 (x INTEGER NOT NULL);
 INSERT INTO T1 VALUES (1);
 INSERT INTO T1 VALUES (2);
 INSERT INTO T1 VALUES (3);
 INSERT INTO T1 VALUES (4);

 CREATE TABLE T2 (x INTEGER NOT NULL);
 INSERT INTO T2 VALUES (1);
 INSERT INTO T2 VALUES (1);
 INSERT INTO T2 VALUES (1);
 INSERT INTO T2 VALUES (1);

Now try to update T1 by doubling all the rows which have a match in T2;
The FROM clause gives you a CROSS JOIN.

 UPDATE  T1
    SET T1.x = 2 * T1.x
   FROM T2
  WHERE T1.x = T2.x;

 SELECT * FROM T1;
    x
 ====
   16
    2
    3
    4

The FROM clause gives you a CROSS JOIN, so you get a series of actions
on the same row (1 => 2 => 4 => 8 => 16). Go one step further and
include another table with no relation to anything:

 CREATE TABLE T3 (x CHAR(1) NOT NULL);
 INSERT INTO T3 VALUES ('a');
 INSERT INTO T3 VALUES ('a');
 INSERT INTO T3 VALUES ('a');
 INSERT INTO T3 VALUES ('a');

 UPDATE  T1
    SET T1.x = 2* T1.x
   FROM T2, T3
  WHERE T1.x = T2.x;

 SELECT * FROM T1;

     x
======
 65536
     2
     3
     4

These are pretty simple examples, as you can see, but you get the idea.
There are subtle things with self-joins and the diseased mutant T-SQL
syntax which can hang you in loops by changing things, or you can have
tables which are dependent on the order of the rows for their results,
etc. They have fixed this problem in later versions of the product,
however, by doing a hidden SELECT DISTINCT in the implied query.

What is supposed to happen is that the UPDATE goes to the UPDATE (or
DELETE FROM clause) to locate the table involved.  It then goes to the
WHERE clause (if it is not there then imagine a WHERE clause that is
always TRUE) and marks all the qualified rows. In the case of the
DELETE FROM statement, it then removes all the marked rows at once. In
the case of the UPDATE statement, it then builds an old image and a new
image of the marked subset. The SET clause then assigns values to the
new image; any column not mentioned in the SET clause stays the same.
The old image subset then replaces the old image subset in the table.

This is why

 UPDATE Foobar
    SET x = y, y = x;

will exchange the values of columns x and y.  Everything is done in
sets, not a row at a time.

--CELKO--
Joe Celko, SQL Guru & DBA at Trilogy
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.veryComputer.com/
Before you buy.