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
