Re: a join between tables and a 'selective' update
Date: Tue, 18 Jan 2005 21:45:47 +0100
Message-ID: <pan.2005.01.18.20.45.37.971406_at_interia.pl>
On Tue, 18 Jan 2005 08:29:40 -0800, DA Morgan wrote:
> Rafal Pietrak wrote: >
>> Hi,
>> I'm not quite sure if this is the best group for this kind of question,
>> But I hope so (the theory tag look encouraging :)
>>
>> I fell into the following problem:
>>
>> I have numerous 'logfiles', (which in reality are implemented as
>> database tables) all close to, but all somewhat different in details as
>> those two:
>> CREATE TABLE logfile1 (tm timestamp,
>> something numeric,
>> something1 varchar,
>> something2 integer references foreign key...,
>> ... etc);
>> CREATE TABLE logfile2 (stamp date, when time,
>> something varchar,
>> something char[4],
>> ... etc);
>> 2. All those 'logfiles' have some sort of timestamp, which I already CAN
>> convert to a common TIMESTAMP value.
>> 3. All those 'logfiles' have some additional info, which one way or
>> another I CAN convert into two fields (user integer, info varchar).
>>
>> By now, I do:
>> CREATE TEMP TABLE window (tm timestamp,
>> user integer, info varchar2);
>>
>> Then, I extract the necessary information from all 'participating' tables
>> by means of:
>> SELECT INTO window tm, something as user, something as info FROM ...
>> SELECT INTO window to_date(stamp) as tm, ....
>> ...
>>
>> just to be able to make the following query:
>> SELECT * FROM window ORDER BY tm;
>> ...so that I can see the sequence of events irrespective of the 'logfile'
>> the event comes from.
>>
>> Is there a simpler (but generic SQL) way to achieve the same output from
>> given 'any logfile' set? One without an explicit creation of a transient
>> WINDOW table?
>>
>> ANOTHER closely related SQL problem is the ability to update just a few
>> records of a table, records, which happen NOT to be distinguishable
>> from one another to program making the query.
>>
>> To explain.
>>
>> Let's have a store, which hold food. One of the products is milk in cans.
>> Every milk can has a unique (but random) serial number (ID) and validity
>> date. When making a selection for a 'transporter' to shopping floor I'd
>> like to select those that have earliest expiry date (are oldest). For the
>> purpose of this problem, pls assume, that the timestamp MAY*NOT appear in
>> query output. So, when shopping floor requests 10 cans, I mean something
>> like:
>>
>> SELECT id FROM milk_store ORDER DESC BY expiry LIMIT 10;
>>
>> (forgive me using the PostgreSQL LIMIT expression - but this is easies way
>> to *present*the*problem) I'm actually looking for a generic SQL phrase,
>> that would have the desired result (the above want work from PostgreSQL
>> either, since the query may not be sorted by 'not displayed' column).
>>
>> Then of course I'd like to be able to mark in database, which cans went
>> out. Meaning something like:
>> UPDATE milk_store SET state=sold WHERE id in (SELECT id FROM
>> milk_store ORDER DESC BY expiry LIMIT 10);
>>
>> Any help appreciated - particularly how to achieve above WITHOUT the LIMIT
>> clause, which I understand is not quite so generic SQL.... note, that I'm
>> searching for an SQL solution, not 'procedural SQL', which at this point
>> is not quite so interesting for me.
>>
>> Regards,
>> -R
> > Your question is product specific ... not theoretical. In some RDBMS > products what you propose to do is abhorant. What is the specific product?
Sorry to here that.
I test all this on PostgreSQL if that's what you ask here (if my english does not misslead me with understanding your question).
But I didn't intend to ask a 'Postgresql way of doing things' question. Experience show, that as time goes by, all those 'peculiarities' of Oracle/Postgres/Sybase/etc... converge into common SQL grounds (sometimes extentions become common, too). So, I'm not looking for an answere hooked into any of those worlds.
I'm looking for a 'standard' SQL-XX way of: "expressing the programmistic reality" - the tables described above. In other words, I more or less figured out a *crude* way to achieve what I need, but I hope there is a clean and elegant way SQL-gurus write such things. I thought may be I could find gurus here, so I can learn those things.
Or to learn, that there is no such clean and elegant way. That SQL is BD and one just have to live with those temporary litter and other nasties.... Or may be someone knows a better group to post the question?
Regards,
-R
Received on Tue Jan 18 2005 - 21:45:47 CET