|
|
Posted by Darren Woodbrey on 11/28/06 16:49
That is it. Thanks so much for your help!
"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
Navigation:
[Reply to this message]
|