Posted by Hugo Kornelis on 11/14/62 11:31

On Mon, 7 Nov 2005 15:08:30 +0800, 001 wrote:

>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

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]