Re: Saving result set of SELECT to table column

On 2008-01-14 Patric wrote:
>   Well I've a normalized database.. 
>   For instance:
> 
>   create table Y ( pk, data...  );
>   create table Z ( pk , data...    );
> 
>   create table X (  char, references  Y, references  Z);
> 
>   SELECT * from X;
> 
>   Now I want to make a listing of the result set from X.
>   If there are references to Z or Y (not null refs), I want to display 
> that data too.
> 
>   Normally I would SELECT, to get that data, not in my case.
>   Nearly all queries will be SELECTs, no UPDATEs or INSERTs, so need to 
> optimize that case.
> 
>   The dirty little denormalization would look like this:
> 
>   create table X ( char, ref. to Y, ref. to Z, StoreY Y , StoreZ Z);
> 
>   On insert or update of Z or Y, I would update these two (StoreY, 
> StoreZ) columns by RULE or TRIGGER..
>   I know this is not nice etc.. Codd would sue for this, but in my case 
> performance over beauty is ok.
>     I'm looking for something like UPDATE X set StoreY=(SELECT * FROM Y 
> WHERE pk=4) WHERE foreignID2Y = 4;
> 
>   Is there a away to accomplish this straightforward  in a single 
> statement without doing loops and stuff in a serverside procedure?

Looks to me like you want to (LEFT|RIGHT) OUTER JOIN the tables.

Regards
Ansgar Wiechers
-- 
"The Mac OS X kernel should never panic because, when it does, it
seriously inconveniences the user."
--http://developer.apple.com/technotes/tn2004/tn2118.html