Re: a join between tables and a 'selective' update

Thank you ALL!

A short roundup for all those, that may fell into the same problems:

1. the 'join' was nicely solved by UNION of SELECTs (once I new what I was looking for, RTFM helped :)
2. the UPDATE I needed (which works OK) turns out to be quite simple, too. All that was needed was '= ANY' instead of 'IN' operator, since 'ANY' *allowes* for multiple column output from SELECT, so I can sort the other column as necessary: UPDATE milk_store SET state=sold WHERE (id,expiry) = ANY (SELECT id,expiry FROM milk_store ORDER BY expiry LIMIT 10);

I understand, that the SELECT sub-query, in other SQL dialects, may have to be written as "SELECT TOP 10 ..." instead of LIMIT, but that's sufficiently portable for me.

Still, the lefthand side of the "= ANY" operator has this spurious "expiry" term, which actual side effects I don't really understand, but a couple of tests I did on postgresql *DOES*YIELD* results I expected, so may be it's OK nonetheless.

Thenx again,

-R
PS: looks like making a fruitful google query is a knowledge by itself :) thank you for a hint -CELKO-. Received on Thu Jan 20 2005 - 11:37:53 CET