RE: UPDATE/INSERT BY NAME
>From: uunet!cix.compulink.co.uk!shemminga (Stuart Hemming)
>Subject: RE: UPDATE/INSERT BY NAME
>Date: Thu, 26 Nov 1992 16:14:00 +0000
>X-Informix-List-Id: <news.2206>
>TITLE: RE: UPDATE/INSERT BY NAME
>In <1f09ikINN7fa@emory.mathcs.emory.edu> Jonathan Leffler replies:
>> I'm not clear what the advantages of the UPDATE BY NAME operation are
>> supposed to be. In interactive SQL, there are no records, so the syntax
>> is irrelevant to dbaccess etc. And in I4GL, all it saves is a little bit
>> of typing for less clarity of program. I don't see any major advantages to
>> the syntax. Against it, it would be highly non-standard and bears no
>> resemblance to anything I know of which is likely to occur in any future
>> SQL standard. I'd have to rate UPDATE BY NAME as a non-starter.
>> I'm even less clear of the advantages of INSERT BY NAME.
>> Someone else suggested CREATE TEMP TABLE LIKE record.* -- similar
>> arguments apply here too.
>> So all in all, I'm not in the least bit convinced. You could try running
>> the proposal past me again since I deleted the original as something I
>> wasn't going to answer, but I don't think it'll make much difference to my
>> views on it
>What can I say? I (still) seems like a good idea to me....
I've just re-read my message and it is a little over emphatic, though I
think it makes my point.
You asked for feedback from Informix, unofficially -- I gave mine. I
noted one supportive response from outside Informix. I haven't checked
with the other "corresponding members" of Informix to obtain their views,
and they may view the matter differently. I've never felt the need for
such a statement in the 6+ years I've been working with Informix products.
My view is not final; it is just a personal, over-stated opinion.
I note that you have not taken the opportunity to re-state your case -- I
did invite such a restatement in my response because, as I say, I don't
have a copy of your original suggestion at hand to look at it again. You
can consider this a second offer to re-examine your suggestion, and I don't
mind whether you send the original again or an expanded or re-worded
version of it.
I do think that the programs would be less clear as a result of using that
construct. The only other places where the BY NAME clause is used is in
the I/O side of I4GL, not the SQL side, so it would involve major changes
to the compiler to support it (not that the change would necessarily be
vetoed on that score).
*** I made a statement here, then went to check it, and found that the
*** following code works OK:
DATABASE forms
MAIN
DEFINE
r RECORD
act LIKE sec_acttab.sat_action,
tab LIKE sec_acttab.sat_tabname,
gor LIKE sec_acttab.sat_grant_revoke
END RECORD,
s RECORD LIKE sec_acttab.*
LET r.act = 'JUNK01'
LET r.tab = 'junk'
LET r.gor = 'G'
UPDATE sec_acttab
SET (sat_action, sat_tabname, sat_grant_revoke) =
('JUNK01', 'junk', 'G')
WHERE sat_sequence = -1;
UPDATE sec_acttab
SET (sat_action, sat_tabname, sat_grant_revoke) = (r.*)
WHERE sat_sequence = -1;
LET s.sat_sequence = -1
LET s.sat_action = "JUNK01"
LET s.sat_tabname = "junk"
LET s.sat_grant_revoke = 'G'
LET s.sat_permissions = "IUD"
UPDATE sec_acttab
SET (sat_action, sat_tabname, sat_grant_revoke) =
(s.sat_action THRU s.sat_grant_revoke)
WHERE sat_sequence = s.sat_sequence
-- This does not compile! (RDS 4.10.UC1)
-- UPDATE sec_acttab
-- SET (sat_action THRU sat_grant_revoke) =
-- (s.sat_action THRU s.sat_grant_revoke)
-- WHERE sat_sequence = s.sat_sequence
END MAIN
Now, apart from having to list the column names on the LHS of the equals,
this supplies all the functionality of your proposal, I think, rendering
most of your proposal redundant, unless your only objection was to having
to list the column names twice. I'd remain to be convinced that the saving
in programmer time from not having to list the names twice is anything but
negligible. Also, if the database changes, you are likely to have to
revise your program under either scheme.
>Stuart
>|8-)
Or did you mean: |8-(
Yours verbosely,
Jonathan Leffler (johnl@obelix.informix.com) #include <disclaimer.h>