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

Thenx,

On Tue, 18 Jan 2005 18:35:06 -0800, -CELKO- wrote:

> 1) >> Is there a simpler (but generic SQL) way to achieve the same
> output from
> given 'any logfile' set? <<
>
> A VIEW with a UNION to put the various tables together

I'm too fresh to SQL to understand immediately how to write it - never actually had to use UNION before :(. I'd appreciate a simple example based on CREATE TABLEs I've quoted in my first post?

> 2) >> the ability to update just a few records [sic] of a table,
> records [sic] , which happen NOT to be distinguishable from one another
[...]
> At this point you use a proprietary version of LIMIT, or I can give you
> a general query for the N-th element based on ordering by serial number
> within expiration date.

Sorry, I must have expressed myself not clearly enough. The whole point here was to be able to UPDATE (the last code snippet in my post).

Of course I can order by serial numbers, but this is not the order I need. Serial numbers are random and don't reflect milk getting 'unfresh'.

So to rephrase the original question: I'd rather UPDATE lets-say:10 (ten - as assumed: requested by salesman) *oldest* milk-can records, and then SELECT those for shipment to the store floor. (that's why I asked for a clue to be able to make an ORDERED SELECT, but with the ordering based on *not*displayed* column (I'm not quite sure is this order-inability is SQL semantics or just postgresql implementation) - by now, I can only think of an UPDATE based on "... WHERE id in (SELECT...)" - for that I needed a SELECT returning just a list of IDs (without timestamp). But may be there is a better way to achieve this 'update of ten arbitrary, but oldest'?)

To make it 'more like real life', assume, that MILK_STORE get loads of milk cans 2-4 times a day, each having 100-1000 cans. Those loads will have an *equal* expiry time, and from such store, 30-100 times a day I'll need to take 3-30 cans at a time, that are currently 'oldest but random within the same load'.

Is this possible with a single query?

Can I: "UPDATE milk_store ..."? And do update *exactly* 10 records out of 'indistinguishable' 100 from a table holding, say 2000?

-R Received on Wed Jan 19 2005 - 10:09:40 CET