Search and replace departments


Phil V

I have a client that currently has 900 departments that they would like
consolidated to 21 deparments (carryover from old POS system). So I've been
using the following SQL query to do this task:
UPDATE ITEM SET Departmentid=(new ID) WHERE Departmentid=(old ID)

Is there a faster way to do this?

Thanks in advance.




Hi Phil - maybe you can use "IN"....lets say department IDs 1, 2, 3, 4, 5, 8
9, 11, and 15 should now be assigned to dept id 23....

update item
set departmentid = 23 where departmentid in (1, 2, 3, 4, 5, 8 9, 11, 15)

Its a good idea to run as a select statement first before doing the update...
select * from item where department id in (1, 2, 3, 4, 5, 8 9, 11, 15)

Don't forget to backup the db before running the update script....

What SQL giveth, SQL taketh away...


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question