Are these two statements equivalent?  If not, how can I rephrase the 2nd
one?

UPDATE Contact
    SET DoNotCallTypeKey = 20
WHERE EXISTS (
    SELECT *
    FROM SSA
        INNER JOIN SSP
            ON SSP.SampleSourceArchiveKey = SSA.SampleSourceArchiveKey
    WHERE ssp.ContactKey = Contact.ContactKey
        AND ssa.SampleSourceKey = @sampleSourceKey
        AND ssa.SurveyFlag = 'DNS'
)

vs

UPDATE Contact
    SET DoNotCallTypeKey = 20
FROM Contact
    INNER JOIN SSP
        ON ssp.ContactKey = ssp.ContactKey
    INNER JOIN SSA
        ON ssa.SampleSourceArchiveKey = ssp.SampleSourceArchiveKey
WHERE ssa.SampleSourceKey = @sampleSourceKey
    AND ssa.SurveyFlag = 'DNS'


They both look like they return the same results, but when it operates on
1.5 million records, I really don't want to have to scroll trhough both
result sets to prove it.  I want to rephrase it because I hate all these
dumb "where exists" things that are upside down with the criteria and joins
all stuffed into sub-selects.

Mike,

They look the same with the exception:

ON ssp.ContactKey = ssp.ContactKey

should be

ON ssp.ContactKey = Contact.ContactKey

HTH

Jerry
Show quoteHide quote

Sorry, that was just a typo.

I'm just looking for confirmation that they're the same.  Thanks.

These people around here come from a MS Access background and they don't
know how to use joins right.  So they just write all these upside down
things with WHERE [NOT] EXISTS and a long list of sub-selects, and it's
almost indecipherable sometimes.

Thank You!

Show quoteHide quote

The first is correct SQL, while the seconnd is proprietary and
problematic.

It makes no sense in terms of the SQL language model.  A FROM clause is
always suppose effectively materialize a working table that disappeare
at the end of the statement.  Likewise, an alias is supposed to act as
it materializes a new working table with the data from the original
table expression in it.  To be consistent, this syntax says that you
have done nothing to the base table.

Sybase and some other vendors had the same syntax but with different
semantics.  Worst of both worlds!

And on top of that, it is unpredictable. This is a simple example from
Adam Machanic

CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);

INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);

CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);

INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);

You run this proprietary UPDATE with a FROM clause:

UPDATE Foo
   SET Foo.col_b = Bar.col_b
  FROM Foo INNER JOIN  Bar
       ON Foo.col_a = Bar.col_a;

The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).

The join mechanism hides cardinality violations, turns your data into
garbage.

On Fri, 16 Sep 2005 14:55:46 -0400, Mike Labosh wrote:

Hi Mike,

Apart from the typo, they might be. But it's also possible that the
second version sets the DoNotCallTypeKey for a row to 20 hundreds of
times during the execution. I'd have to know the table structure to be
sure.

But there are some important other issues that you should think about.

First: The first statement is ANSI standard SQL, that will eaasily port
to other database platforms. The second is proprietary syntax that runs
fine on SQL Server, but can't be ported to other databases. Even MS' own
"other" database product (Access) won't run this code - it has a similar
non-ANSI syntax for UPDATE and DELETE, but it's not the same as in SQL
Server!

Second: There are definitely situations where I would choose to use the
non-standard UPDATE ... FROM syntax. But this is not one of them. I
would consider using the proprietary syntax if the new value for the
DoNotCallTypeKey had to be taken from one of the tables in the subquery
(as SQL Server isn't very clever about optimizing statements that have
the same subquery twice).

Third: Even on SQL Server, the second syntax will sometimes fail. If
Contact is not a base table, but a view, AND you have an INSTEAD OF
UPDATE trigger defined on Contact, you'll get an error if you try the
second syntax. SQL Server somehow doesn't know how to handle this
situation.

Fourth:
I consider this to be a very bad reason. Personal bias should always
come second to professional impartiality.

Instead of trying to get your Access developers to learn a syntax that
won't work on Acces, you'd be better off getting yourself acquainted
with the syntax that will work on all SQL-92 compliant databases. You'll
also find that this syntax grows on you when you use it more often (as I
found out when I had to rewrite dozens of UPDATE ... FROM statements
because the view they updated had to be equipped with an ISNTEAD OF
trigger).

Best, Hugo

PRIMARY KEY
Another case for IDENTITY
Query with user-define function
Trigger - across databases rights?
Heterogeneous tree and foreign key relationship