Sql update statement hangs in sql2000 but not in sql2005

[nubianwarrior]

I'm running an update statement in a sql2000 environment that hangs and gives no error. But if I run the same statement in a sql2005 environment it runs fine. I'm using the same database on both environments. Here is the statement.....

UPDATE F_PARTS
SET F_PARTS.PA_ON_ORDER = ISNULL(F_PARTS.PA_ON_ORDER,0)+ISNULL(ORIG.PA_ON_ORDER,0)
FROM UPDATEPARTS
inner JOIN F_PARTS ON UPDATEPARTS.NEW_PA_FK = F_PARTS.PA_PK
join (
select F_PARTS.pa_pk, F_PARTS.PA_CODE,F_PARTS.PA_ON_ORDER
FROM UPDATEPARTS
JOIN F_PARTS ON UPDATEPARTS.PA_PK = F_PARTS.PA_PK
WHERE UPDATEPARTS.NEW_PA_FK IS NOT NULL
) as orig ON ORIG.pa_pk = updateparts.pa_pk
WHERE UPDATEPARTS.NEW_PA_FK IS NOT NULL
and updateparts.pa_pk <> updateparts.new_pa_fk


is this a versioning issue or a sql issue?

 

[bborissov]

How about:

UPDATE F_PARTS SET F_PARTS.PA_ON_ORDER = ISNULL(F_PARTS.PA_ON_ORDER,0)+ISNULL(ORIG.PA_ON_ORDER,0)
FROM F_PARTS
INNER JOIN UPDATEPARTS ON UPDATEPARTS.NEW_PA_FK = F_PARTS.PA_PK AND 
                          UPDATEPARTS.NEW_PA_FK IS NOT NULL     AND
                           updateparts.pa_pk <> updateparts.new_pa_fk

No need of any derived tables and complicated selects
(not tested though)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[nubianwarrior]

Thanks for the reply.....

When I tried this it failed with column prefix 'ORIG' does not match with a table name or alias name used in the query.

Its almost as if this is running in an infinite loop. Anyone else hav any ideas....???

 

[SQLBill]

Remove the orig from Boris' post. That is a left-over alias from your original derived table.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[SQLBill]

Also, even though you say it is the same database on both SS2000 and SS2005, you may want to check to make sure the Indexes are the same.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875