Hello,

The select statement needs only 1 second to complete the query.
But the update statement spends 30 minutes. Why?


SELECT STATEMENT:
declare @IDate smalldatetime
select @IDate=col001 from USDay
select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010 is not null


UPDATE STATEMENT:
update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010 is not null


INDEX:
clustered index: US990020, US990010
non-unique index: US990010, US990020

001 (001@ms8.url.com.tw) writes:
[quoted text, click to view]


Unless there is a blocking issue, I would suspect it is because the
US99000D is not match against the instance in the derived table. How this
work:

update US99000D
set US990030=A.col003,
US990040=A.col004,
US990050=A.col005,
US990060=A.col006,
US990070=A.col007
from US99000D
join USDay A on A.col001=B.US990010 and A.col002=B.US990020
where B.US990010=@IDate


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

It works fine. But I don't understand why you said *the
US99000D is not match against the instance in the derived table* ?

Peter Yu (yuyuante@ms3.hinet.net) writes:
[quoted text, click to view]

The UPDATE FROM syntax is proprietary for MS SQL Server and Sybase. It
works well most of the time, but there are some funny tweaks when you
leave the main row. I recommend that you always include the target table
as the first table in the FROM clause to avoid accidents like this. What
happened now was that you probably got a cartesian product between the
target table and the tables in the FROM clause.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[quoted text, click to view]

Hi 001,

The proprietary UPDATE FROM syntax has many issues. One of them is that
these two versions are considered equal:

UPDATE Table1
SET Something = SomethingElse
FROM Table2
WHERE xxx = yyy

or

UPDATE Table1
SET Something = SomethingElse
FROM Table2, Table1
WHERE xxx = yyy

The technicalities: the table name to be updated is looked for in the
FROM clause. If one match is found, that table is used. If two matches
are found, an error is raised (you'll have to use an alias in that
case). And if no matches is found, the table is implicitly added to the
FROM clause.

Since the tables in your UPDATE's FROM clause are called USDay, with
alias A and (no name for derived table), with alias B, the table name
US99000D is added to the FROM clause. Your UPDATE is equivalent, not to
the SELECT statement above, but to this one below:

select * from USDay A
join (
select US990010, US990020, US990030, US990040, US990050, US990060,
US990070 from US99000D where US990010=@IDate
) B on A.col001=B.US990010 and A.col002=B.US990020
CROSS JOIN US99000D
where B.US990010 is not null


The best way to prevent these problems is two teach yourself the
following guidelines:

1. Avoid the proprietary UPDATE ... FROM and DELETE FROM ... FROM when
ever possible. Only use it if ANSI syntax has distinct disadvantages.

2. If you have to use UODATE ... FROM, *always* include all tables
(including the one to update) in the FROM; *always* give all tables in
the FROM an alias, and (and here comes the hammer) **always** include
the _alias_, not the table name after the UPDATE keyword.

Example:
UPDATE a
SET Something = b.SomethingElse
FROM Table2 AS b
INNER JOIN Table1 AS a
ON a.xxx = b.yyy


Best, Hugo
--