Totally New to SQL -- Trying to Move an ID within same table

Transact-SQL
1

Hi,

I'm totally new to SQL and am trying to move and Id from an OrganizationID to a different Organization ID. They're in the same table. I tried writing a few update scripts, but it's not working as expected. I need help as it would make my project a lot easier if I had this function.

2016-06-29 09_25_27-update_script.png726×270 28.5 KB

Can someone please help me?

2

What have you tried? What did you expect but not see?

BEGIN TRAN;
UPDATE YourTable
SET OrganizationId = '7F....'
WHERE ID = '71E....';

ROLLBACK TRAN;
2 Likes
3

Well, kind of embarrassing...but I looked at an update script where a person was moved from one organizationID to another, so I used that as a model. Here's what I did (don't laugh):

UPDATE OnboardingPackage
SET OrganizationId = P.OrganizationID
FROM MY_Demo.dbo.OnboardingPackage OP
INNER JOIN MY_Demo.dbo.Person GTP
ON GTP.Id = OP.Id
WHERE (GTP.OrganizationId NOT IN ('71E1E177-4621-4AB6-8145-07B506ABDFFC', '7F1BDC6F-CFF9-4181-85F4-AFD223EC51D8'))

I see something I need to change...I think it's that P.OrganizationID...should be maybe OP? Not sure since this was taken from moving a person...(person table).

4

But see, I have to move that ID, 71E1E177-4621-4AB6-8145-07B506ABDFFC, from its OrganizationID, B2015F8B-9AA6-4589-9346-3C929181C719, to this OrganizationID, 7F1BDC6F-CFF9-4181-85F4-AFD223EC51D8.

I'm missing something.

5

Think of it as associations. The Id column presumable is an Id of a person, or a business process, or a software package or something. That Id (in your example, 71E1E177-4621-4AB6-8145-07B506ABDFFC) is currently associated with (or assigned) , organization B2015F8B-9AA6-4589-9346-3C929181C719. You want to associate a different organization with that Id. So you want to update the organization column for the Id.

The code that @djj55 posted will do that. He has a rollback statement there - which is there just so you can revert the changes if you needed to. So I would try this:

BEGIN TRAN;
UPDATE YourTable
SET OrganizationId = '7F1BDC6F-CFF9-4181-85F4-AFD223EC51D8'
WHERE ID = '71E1E177-4621-4AB6-8145-07B506ABDFFC';

SELECT * FROM YourTAble WHERE ID = '71E1E177-4621-4AB6-8145-07B506ABDFFC';

Run that and verify that the organization id has been updated correctly. If you are satisfied and want to keep the updated version run this:

COMMIT

If you don't want it, and want to revert the change, run this:

ROLLBACK

Be sure to run either the rollback or the commit.

2 Likes
6

OMG! :scream: Both you and djj55 are awesome! This totally works. I was overthinking the whole thing.

Thank you both sooo much! :grinning:

7

Thank you so much djj55! I am so friggin' happy right now...saved me so much time! :grinning: Both you and JamesK were right on!