This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_001_01C17B81.6EE1A004
Content-Type: text/plain;
	charset="iso-8859-1"

It looks to me as if you have an extra WHERE clause, I count 4 for 1 UPDATE
and 2 SELECTs. Apart from that you can considerably simplify this statement.

UPDATE tblOrder 
SET backOrder = os.backOrder, <more assignment> 
FROM tblOrderStack os 
WHERE statusID = (SELECT statusID 
                 FROM tblOrderStack 
                 WHERE orderID = 1980
                 HAVING stackSequence = MAX(stackSequence))

Unless I have missed the point, this should do what you want.

regards
David Cameron
nOw.b2b
dcameron@i...

>  -----Original Message-----
> From: 	Mark.Chen [mailto:Mark.Chen@s...] 
> Sent:	Saturday, 1 December 2001 2:40 AM
> To:	sql language
> Subject:	[sql_language] UPDATE FROM  a subquery?
> 
> Hi there, 
> 
> I am trying to write a query with UPDATE FROM clause. Assuming that the
table after FROM can be replaced by a subquery, I did it this way:
> 
>   update tblOrder 
>   set backOrder = os.backOrder, <more assignment> 
>    FROM tblOrderStack os 
>           WHERE (statusID = (SELECT statusID 
>                              FROM tblOrderStack 
>                              WHERE stackSequence = (SELECT
MAX(stackSequence) As stackSequence 
>                                                     FROM tblOrderStack 
>                                                     WHERE orderID = 1980 
>                                                    ) 
>                             ) 
>                  )AND 
>                 (orderID = 1980) 
>           
>    WHERE orderID = 1980 
> 
> However, I can never get the sytax right. The interpreter keeps on
consider the last where clause to be redundant, suggesting that the where
clause in the subquery is mistaken as the where clause for the UPDATE.
> 
> What mistake am I making here? Can I use subquery here at all? 
> 
> Thanks, 
> 
> Mark 
> 
> 
$subst('Email.Unsub')
> Read the future with ebooks at B&N
>
http://service.bfast.com/bfast/click?bfmid=2181&sourceid=38934667&categoryid
=rn_ebooks