2

Say I have the following table:

App   owner   owner_id
______________________
1     John    NULL
2     Jack    000123
3     John    NULL
4     April   000124
5     John    NULL
6     April   000124
7     John    000123
8     Ash     NULL
9     Ash     NULL
10    Ash     NULL

If I need to update John and Ash's owner_ids, is there a way to update each owner's owner_id for every occurrence in the table by only entering the information once?
I have to do this for about 1000 owners, but with the amount of duplicates I am looking at around 10000 blank owner_ids.

I do not have the proper rights to restructure or split the table.

Improve this question
1
  • Where do you get the owner_ids from? Commented Jun 28, 2013 at 16:49

1 Answer 1

Reset to default
4

If I didn't misunderstand your question, it's just:

update owners_table set owner_id = '000001' where owner = 'john'
update owners_table set owner_id = '000002' where owner = 'ash'

This will set the owner_id to 000001 in every row where the owner is John, and to 000002 in every row where the owner is Ash.

It's just more copy & paste when you have a lot of owners, because you have to create one query for each owner.

Another way would be to create a temporary table with just the owner and his new id:

owner   newid
--------------
John    000001
Ash     000002

You can enter hundreds or thousands of owners and their new owner_ids into the table.

Then, you can update them in your main table all at once with the following query:

UPDATE owners_table 
INNER JOIN ownertmp ON owners_table .owner = ownertmp.owner 
SET owners_table.owner_id = [ownertmp].[newid];

After that, you can delete the temporary table again - it's just needed to run the query.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.