Hi Mark, yes you're right in case of multiple items the order should be reverse.
Claudia Am 26.03.2010 14:55, schrieb Mark H. Wood:
On Fri, Mar 26, 2010 at 07:52:59AM +0100, Claudia Jürgen wrote:1. update item set owning_collection=NewCollectionID where item_id in (select item_id from item where owning_collection=OldCollectionID); 2. update collection2item set collection_id=NewCollectionID where collection_id=OldCollectionID and item_id in (select item_id from item where owning_collection=OldCollectionID);It looks to me as though step 2 needs to happen before step 1, since after step 1 there should be no rows in item with owning_collection = OldCollectionID. I don't need the need for the interior SELECT in step 1 either. Would this work: 1. UPDATE collection2item SET collection_id = NewCollectionID WHERE collection_id = OldCollectionID AND item_id IN (SELECT item_id FROM item WHERE owning_collection = OldCollectionID); 2. UPDATE item SET owning_collection = NewCollectionID WHERE owning_collection = OldCollectionID; [unnecessary polishing follows] I suspect that the second WHERE term in the update of collection2item is not needed, since I can't think of a reason for collection2item.owning_collection to have a value X unless the row in item with the same item_id value also has owning_collection = X. [sticking my neck out even further] In fact, I don't see why we have collection2item at all. Isn't it simply equivalent to a secondary index over item.owning_collection? (There is also a foreign-key reference constraint on collection2item.owning_collection, but couldn't that be transferred to item as well?) ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech
<<attachment: Claudia_Juergen.vcf>>
------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev
_______________________________________________ DSpace-tech mailing list DSpace-tech@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/dspace-tech