Posted by Roy Harvey on 11/28/06 16:31

I see two problems. One is that the subquery uses TOP without an
ORDER BY. Without ORDER BY, TOP simply returns any one row.

The other is that the subquery's FROM clause should NOT include the
table being updated.

Try something along the lines of this:

UPDATE HPFSLOWMOVING
SET LASTRCTDATE =
(SELECT TOP 1 DOCDATE
FROM IV30300
WHERE HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR
AND HPFSLOWMOVING.LOCNCODE = IV30300.LOCNCODE
AND DOCTYPE = 4
ORDER BY ??????)

Roy Harvey
Beacon Falls, CT

On Tue, 28 Nov 2006 11:13:55 -0500, "Darren Woodbrey"
<it@hpfairfield.com> wrote:

>My query below is wrong. This is what I have so far:
>
>UPDATE HPFSLOWMOVING
>SET LASTRCTDATE = (SELECT TOP 1 DOCDATE FROM IV30300 INNER JOIN
>HPFSLOWMOVING ON HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR AND
>HPFSLOWMOVING.LOCNCODE = IV30300.LOCNCODE WHERE DOCTYPE = 4)
>
>
>
>"Darren Woodbrey" <it@hpfairfield.com> wrote in message
>news:12monclfgt9923a@corp.supernews.com...
>>I am trying to update 1 table with the top records from another table for
>>each record in the first table
>>
>> UPDATE HPFSLOWMOVING
>> SET TOP 1 LASTRCTDATE = (SELECT DOCDATE FROM IV30300 INNER JOIN
>> HPFSLOWMOVING ON HPFSLOWMOVING.ITEMNMBR = IV30300.ITEMNMBR AND
>> HPFSLOWMOVING.LOCNCODE = IV30300.LOCNCODE WHERE DOCTYPE = 4)
>>
>> This updates all records with the same lastrctdate. I need to update each
>> records with the top lastrctdate where the itemnmbr and locncode equals.
>> Thanks for any help you can provide!
>>
>>
>> Darren

[Back to original message]