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

"Roy Harvey" <roy_harvey@snet.net> wrote in message
news:uvoom2tk8puai2qv061fr23fkhdqargcg9@4ax.com...
>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

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