SQL for bulk change of Location

In updating the item sequence you are updating the ITEM table and there are issues about doing this in bulk detailed in the Bulk updates of the ITEM table article that you need to be careful of.

In terms of the SQL because you are looking for a classmark range you will need to look at the CLASSIFICATION table as well as the ITEM table.

Before updating a table it is always worth checking which rows will be selected by a set of conditions. The SQL would be something like

select * from ITEM
where ITEM.SEQUENCE_ID="2FL"
and ITEM.CLASS_ID in
(select CLASSIFICATION.CLASS_ID from CLASSIFICATION
where CLASSIFICATION.FILING_KEY between "350" and "399.999" )

The actual sequence id is held in the ITEM table and not an numeric id value so it is not necessary to link through to the ITEM_DESCRIPTION_TYPE table that has the sequence name values. Double check that only the items expected are retrieved.

if you are happy that the rows returned are correct then the update command would be

update ITEM
set SEQUENCE_ID = "1FL"
where ITEM.SEQUENCE_ID="2FL"
and ITEM.CLASS_ID in
(select CLASSIFICATION.CLASS_ID from CLASSIFICATION
where CLASSIFICATION.FILING_KEY between "350" and "399.999" )

As ever test anything you do on the MIS server first as there is no undo option. Also as you may have performance issues in updating the ITEM table as detailed the the article above you may have to run the SQL in batches of ITEM_ID's as also discussed in the article e.g. with the line below added to the SQL.

and ITEM.ITEM_ID between 1000 and 2000

Brian Crampton
Developer, Talis