sql - Doing UPSERT when row is referenced by a FK -

-

let's have table of items, , each item, there can additional information stored it, goes second table. additional information referenced fk in first table, can null (if item doesn't have additional info). 

table item (     ...     item_addtl_info_id integer )  constraint fk_item_addtl_info foreign key (item_addtl_info)     references addtl_info (addtl_info_id)  table addtl_info (     addtl_info_id integer not null     generated default      identity (         increment 1         no cache         ),     addtl_info_text varchar(100)     ...     constraint pk_addtl_info primary key (addtl_info_id) )

what "best practice" update item's additional info (in ibm db2 sql, preferably)?

it should upsert operation, meaning if additional info not yet exist new record created in second table, if does, updated, , fk in first table not change.

so imperatively, logic:

upsert(item, item_info): case when item.item_addtl_info_id null     insert addtl_info (item_info)     update item.item_addtl_info_id (addtl_info.addtl_info_id)                                                ^^^^^^^^^^^^^ else     update addtl_info (item_info) end

my main problem how newly inserted addtl_info row's id (underlined above). in stored proc can request id sequence , store in variable, maybe there more straightforward way. isn't comes time when programming databases?

i mean, i'm not interested in id of addtl_info record long remains unique , referenced properly. using sequences seems bit of overkill me in case.

as matter of fact, upsert operation should part of sql language standard operation (maybe is, , don't know it?)...

the syntax looking is:

select * new table ( insert phone_book values ( 'peter doe','555-2323' ) )

from wikipedia (http://en.wikipedia.org/wiki/insert_%28sql%29)

this how refer record inserted in table.

my colleague called construct "in-place trigger", is...

here first version put compound sql statement:

begin atomic declare addtl_id integer; set addtl_id = (select item_addtl_info_id item item.item_id = xxx); if addtl_id null     set addtl_id = (select addtl_info_id new table                      (insert addtl_info                         (addtl_info_text)                         values ('my brand new additional info')                     )                   );     update item set item.item_addtl_info_id = addtl_id     item.item_id = xxx;  else    update addtl_info set addtl_info_text = 'my updated additional info'    addtl_info.addtl_info_id = addtl_id; end if; end

xxx being equal item id updated - code can inserted sproc, , xxx can converted input parameter.

i tried using merge into, couldn't figure out syntax updating table different specified target.


Comments

Post a Comment

Popular posts from this blog

php - What is the difference between $_SERVER['PATH_INFO'] and $_SERVER['ORIG_PATH_INFO']? -

-
what’s difference between $_server['path_info'] , $_server['orig_path_info'] ? how use them? when run print_r($_server) , path_info , orig_path_info not present in array. why not? how can enable them? i have read php manual on them, still don’t understand them. the path_info variable present if invoke php script this: http://www.example.com/phpinfo.php/hello_there it's /hello_there part after .php script. if don't invoke url that, there won't $_server["path_info"] environment variable. the porig_ prefix uncommon. path_info standard cgi-environment variable, , should never prefixed. did read that? (there issues around php3/php4 if invoked php interpreter via cgi-bin/ - hardly has such setups today.) for reference: http://www.ietf.org/rfc/rfc3875
Read more

fortran - Function return type mismatch -

-
i'm attempting recode old c++ program in fortran make use of lapack (i'm aware c++ have lapack++, i'm having lot of trouble installing it, gave up). i didn't have problems compilation, when had variables declared real . when started coding section of program required lapack, found parameters passed dsyev need double precision . tried change double precision (including changing hard coded numbers double precision counterparts, i.e. 0.0 -> 0.0d0) when try compile, following error of functions , subroutines i've written: error: return type mismatch of function <function> @ (1) (real(4)/real(8)) i'm not sure coming from, in program has been changed double precision. for example, i've declared following: double precision :: alpha(3),d(3),zeta1,zeta2 double precision :: a1(3),a2(3),d1(3),d2(3) double precision :: pi pi = 3.14159265359d0 alpha = (/0.109818d0, 0.405771d0, 2.22766d0/) d = (/0.444635d0, 0.535328d0, 0.154329d0 /) 10 i=1,3 ...
Read more

queue - mq_receive: message too long -

-
i implementing communication between 2 processes using queue. problem when call function mq_receive, error: message long. i have done following: struct mq_attr attr; long size = attr.mq_msgsize; .... // initializing queue "/gateway" int rc = mq_receive(gateway, buffer, size, &prio); if print size value, size=1, while when print same size program (got same mechanism), not long integer ( -1217186280 )... how can solve error?....so while size = 1, believe it's right "message long" why 1? p.s. have tried put : int rc = mq_receive(gateway, buffer, sizeof(buffer), &prio); but no result. it seems need read docs more carefully. when call mq_receive should pass size of destination buffer. this size must greater mq_msgsize attribute of queue . in addition, seems have error in queue attributes initialisation makes proper mq_receive call impossible. here standard message queue session: fill mq_attr struct ( doc ): struct mq_a...
Read more