Re: [firebird-support] Re: Insert into Select
That's just an "anonymous" procedure. I suppose it is technically something a client can run without having to define it in the database first - but I was hoping for something using just insert & select. I didn't think it was possible but had to ask. -- Daniel On Sat, Nov 30, 2019 at 22:17, Norbert Saint Georges n...@tetrasys.eu [firebird-support] wrote: dmil...@amfes.com (mailto:dmil...@amfes.com) [firebird-support] a écrit : As I said - I can do this via stored procedures - but is there a way to do it without? execute block -- Norbert Saint Georges http://tetrasys.fi (http://tetrasys.fi)
[firebird-support] Re: Insert into Select
dmil...@amfes.com [firebird-support] a écrit : > As I said - I can do this via stored procedures - but is there a way to do it > without? execute block -- Norbert Saint Georges http://tetrasys.fi ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
[firebird-support] Insert into Select
I know I can achieve this functionality via a stored procedure - but can this be done with "pure" SQL? Given the following basic tables: create domain D_ID as integer not null; create domain D_NAME as varchar(50) not null; create table PARENTS ( PARENT_ID D_ID not null, PARENT_NAME D_NAME not null); alter table PARENTS add constraint PK_PARENTS primary key (PARENT_ID); alter table PARENTS add constraint UNQ_PARENT_NAME (PARENT_NAME) using index IDX_PARENT_NAME; create table CHILDREN ( CHILD_ID D_ID not null, PARENT_ID D_ID not null, CHILD_NAME D_NAME not null); alter table CHILDREN add constraint PK_CHILDREN primary key (CHILD_ID); alter table CHILDREN add constraint UNQ_CHILD_NAME (CHILD_NAME) using index IDX_CHILD_NAME; alter table CHILDREN add constraint FK_CHILDREN_PARENT_ID foreign key (PARENT_ID) references PARENTS(PARENT_ID) on delete cascade on update cascade using index IDX_CHILDREN_PARENT_ID; and there are before insert triggers with generators for the primary keys. The following should be a valid select: select CHILD_ID, PARENT_NAME, CHILD_NAME from CHILDREN natural join PARENTS; Now - given all the above, is there a way of performing: update or insert into (select CHILD_ID, PARENT_NAME, CHILD_NAME from CHILDREN natural join PARENTS) values ( 0, 'parent name', 'child name') matching (PARENT_NAME, CHILD_NAME) returning (CHILD_ID); In the example above the before insert triggers assign a generator value for ID's less than 1. As I said - I can do this via stored procedures - but is there a way to do it without? -- Daniel
Re: [firebird-support] What key word specifies a search for an entire word?
Mark, You're right. My brain was asleep. I've been using OR. Had never tried AND. Karol, Thanks for clarifying. On Sat, Nov 30, 2019 at 5:10 AM Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > >>Not sure what pattern is. > > > > Pattern replace unknown leter(s) sumbols are „%” multiple letters, „_” > single letter > > e.g. > > ‘Ne%flix’ will find Neflix, Netflix, Netflix …. > > > > >>I prefer the word LIKE. Is easier to avoid errors vs. using '='. Are > there advantages to using '='? > > > > If you use like with param Firebird cannot use index on field firstName or > e.g. expression index Upper(firstName) > > > > As your param have unknown value. It can be ‘Netflix’ or ‘%Netflix’, or > whatever > > But if you use „=” it simply can use index if such exists. > > > > Regards, > > Karol Bieniaszewski. > > >
ODP: [firebird-support] What key word specifies a search for an entire word?
>>Not sure what pattern is. Pattern replace unknown leter(s) sumbols are „%” multiple letters, „_” single letter e.g. ‘Ne%flix’ will find Neflix, Netflix, Netflix …. >>I prefer the word LIKE. Is easier to avoid errors vs. using '='. Are there >>advantages to using '='? If you use like with param Firebird cannot use index on field firstName or e.g. expression index Upper(firstName) As your param have unknown value. It can be ‘Netflix’ or ‘%Netflix’, or whatever But if you use „=” it simply can use index if such exists.. Regards, Karol Bieniaszewski.
