Is this correct?

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.
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneMike,
They look the same with the exception:
ON ssp.ContactKey = ssp.ContactKey
should be
ON ssp.ContactKey = Contact.ContactKey
HTH
Jerry
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
> 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.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> They look the same with the exception:
> ON ssp.ContactKey = ssp.ContactKey
> should be
> ON ssp.ContactKey = Contact.ContactKey
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!
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OcA08JvuFHA.3864@.TK2MSFTNGP12.phx.gbl...
> Mike,
>
> HTH
> Jerry
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:Ou%23dxAvuFHA.908@.tk2msftngp13.phx.gbl...
>|||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:

>Are these two statements equivalent? If not, how can I rephrase the 2nd
>one?
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 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.
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
--
(Remove _NO_ and _SPAM_ to get my e-mail address)