update using a subquery

[shavon]

Hello everyone:

Please help with the following:

I want to update the following table:

Dnum Rev Final
111 a
111 b
111 c y
222 a
222 b
222 c y
333 a
333 b y
444 c y

I am updating the highest revision to TRUE.

I have been playing with the following queries in various configurations, but it fails.

--------
Update table1 Set Final=true where exists (select dnum, max(rev) from table1 group by dnum)
--------
update table1 set final=true where exists(Select b.dnum, max(b.rev) from table1 as b inner join table1 on b.dnum = table1.dnum and b.rev=table1.rev group by b.dnum)
--------

Any help would be greatly appreciated.

Thank you

 

[PHV]

And what about this ?
UPDATE table1 A SET Final=True
WHERE Rev=(SELECT Max(Rev) FROM table1 WHERE Dnum=A.Dnum)

Or this ?
UPDATE table1 SET Final=True
WHERE Rev=DMax("Rev", "table1", "Dnum=" & [Dnum])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[shavon]

Beautiful...Just beautiful. Although I feel silly for not seeing that.

Thank you