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.comwrote:
>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.comwrote in message
news:12*************@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