mysql - How to copy records to a table, but not if they are already there -

-

i have 3 tables use filing mechanism training documents, document table (a30), training manual table, stores titles , ids of different training manuals (a36trnman), , table store id's of documents associated different manuals (a31).

in application, allow user copy training manual, along associated documents , make own. however, run problem because different training manuals may include same documents, , there duplicates of document in training manual(which may have modified, , want preserved). so, need duplicate training manual query checks see if document in manual being copied exists user, , if does, leave in place, , create new record in table a31 associates existing (old) document training manual has been added.

my documents

create table `a30` (   `docname` varchar(250) not null,   `docfile` varchar(300) not null,   `docfilepdf` varchar(300) not null,   `ctgry` int(8) unsigned zerofill not null,   `subctgry` int(8) unsigned zerofill not null,   `id` int(8) unsigned zerofill not null auto_increment,   `creator` int(8) unsigned zerofill not null,   `smmry` varchar(60) not null,   primary key  (`id`) ) engine=myisam  default charset=utf8 comment='haha!! training materials!' auto_increment=17 ;   create table `a31` (   `pntryid` int(8) unsigned zerofill not null auto_increment,   `pdocid` int(8) unsigned zerofill not null,   `trnmannum` int(8) unsigned zerofill not null,   primary key  (`pntryid`) ) engine=myisam  default charset=utf8 comment='document pantry' auto_increment=43 ;   create table `a36trnman` (   `unik` int(8) not null auto_increment,   `user` int(8) not null,   `ctgryname` char(60) not null,   primary key  (`unik`) ) engine=myisam  default charset=utf8 auto_increment=88 ;

my current query 1. copies training manual, 2. stores training manual id, 3. copies list of associated document id's table a31, 4. copies documents table a30. how have query check existing duplicates in a30 , leave them be, still add entry a31 reference new training manual? 

set @userid =  <code type="user" />;  create temporary table `a1t` engine=memory select * `a36trnman` `unik` =  '<input     name="unik" hidden="y" />';   update a1t set `unik` = null;   insert `a36trnman` (select * `a1t`);   drop table `a1t`; set @lastid := last_insert_id();  create temporary table `a1t` engine=memory select * `a31` `trnmannum` =  '<input     name="unik" hidden="y" />';   update a1t set `pntryid` = null, `trnmannum` = @lastid;   insert `a31` (select * `a1t`);   drop table `a1t`;  create temporary table `a30t` engine=myisam select docname, docfile, docfilepdf, ctgry, subctgry, id, creator, smmry `a30` left join `a31` on a30.id = a31.pdocid `trnmannum` = @lastid;   update a30t set `id` = null, `creator` = @userid;   insert `a30` (select * `a30t`);   drop table `a30t`;

don't think can current schema

ideal way add column documents table , populate uniqueid. change document links table use instead of auto inc id.

then insert documents weren't in there based on new identifier, never change, auto increment id must.

the links table work you'd using new immutable key.

so baically documents have 2 unique identifiers, 1 auto inc , 1 mysql's equivalent of guid.


Comments

Post a Comment

Popular posts from this blog

django - How can I change user group without delete record -

-
on website user have 1 group. , user can change group. it's made user.groups.clear() and user.groups.add(new_group) but it's not efficient, because there 2 sql query: delete, insert. how can change group update query? user , group related each other using manytomanyfield . means intersection table exists relating both entities, , if don't specify model map (using through attribute) django creates 1 you. looking @ sources django.contrib.auth.models see that's case. fortunatly, can access intermediary model using through attribute of manager (in case, user.groups.through ). can use regular model. example: >>> alice = user.objects.create_user('alice', 'alice@example.com', 'alicepw') >>> employee = group.objects.create(name='employee') >>> manager = group.objects.create(name='manager') >>> alice.groups.add(employee) >>> alice.groups.all() [<group: employee...
Read more

java - Need to add SOAP security token -

-
i need set custom soap header attribute on jax-ws generated webservice client. case webservice calls must go through proxy server requiring specific token (recieved web request header) present in soap request header. e.g.: 1 carserviceservice service = null; 2 service = new carserviceservice(new url(url), new qname(qname); 3 carserviceendpoint port = service.getcarserviceport(); it seems in line 3 wsdl retrieved , call fails due missing security token. 1 point direction on how done? a detailed example has been mentioned here: creating , deploying jax-ws web service on tomcat 6 this article shows how create , use security token .
Read more

java - EclipseLink JPA Object is not a known entity type -

-
i have strange problem eclipselink , object want persist. have 1 object (keypointlistimpl) stores object keypointimpl in list. persisting keypointimpl objects works great if try persist keypointlistimpl object java.lang.illegalargumentexception says object keypointimpl isn't known entity type. here keypointimpl code: @entity @table(name="keypoints") public class keypointimpl implements keypoint { @id @generatedvalue(strategy = generationtype.identity) private int id; @enumerated(enumtype.string) private detectortype keypointtype; private float x; private float y; private float size; private float angle; private float response; private int octave; private int classid; ... } here keypointlistimpl code: @entity @table(name="keypointlists") public class keypointlistimpl implements keypointlist { @id @generatedvalue(strategy = generationtype.identity) private int id; @onetoone(cascade={ca...
Read more