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?)
--
Mark H. Wood, Lead System Programmer [email protected]
Balance your desire for bells and whistles with the reality that only a
little more than 2 percent of world population has broadband.
-- Ledford and Tyler, _Google Analytics 2.0_
pgpEoTV4tprwK.pgp
Description: PGP signature
------------------------------------------------------------------------------ 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 [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-tech
