Tricky SQL

I have three tables.

party
deal
migrated_deal_party


migrated_deal_party table:


old_party_id                   old_deal_id                    new_deal_id                           new_party_id
--------------                   --------------                   ---------------                          ----------------
1111                              2222
3333                              4444



I want to populate the two "new" ids with the new GUIDs that have been created in the respective tables (party, deal)

party table:

old_party_id                   new_party_id
--------------                   --------------
1111                              f5d54936-ed9f-48b1-8908-001e927b2092



deal table:

old_deal_id                   new_deal_id
--------------                   --------------
1111                              f5d54936-ed9f-48b1-8908-001e927b2093


Can someone help me figure out how to perform this action?

I have been using something like this, for my "non-join" tables, but I can't figure out how to change this to meet this situation...

UPDATE deal SET deal.deal_account = account.account_id
FROM deal LEFT OUTER JOIN account
ON account.id = deal.orig_account



thanks for any help in advance!!

cdfllc


LVL 1
cdfllcAsked:
Who is Participating?
 
rafranciscoConnect With a Mentor Commented:
Try this:

UPDATE A
SET new_party_id = B.new_party_id
FROM migrated_deal_party A INNER JOIN party B
    ON A.old_party_id = B.party_id

UPDATE A
SET new_deal_id = B.new_deal_id
FROM migrated_deal_party A INNER JOIN deal B
    ON A.old_deal_id = B.deal_id
0
 
rohanbairat3Commented:
I am not getting your question ....

U have three tables
party
deal
migrated_deal_party

and ur update statement is
UPDATE deal SET deal.deal_account = account.account_id
FROM deal LEFT OUTER JOIN account
ON account.id = deal.orig_account

whats the account table for ?????

if you want to update the new_party_id from the party table u can use the following query

Update migrated_deal_party
set migrated_deal_party.new_party_id = party.new_party_id from Party
 where migrated_deal_party.old_party_id  = party.old_party_id

same goes with the other table

Update migrated_deal_party
set migrated_deal_party.new_deal_id = deal.new_deal_id from deal
 where migrated_deal_party.old_deal_id  = deal.old_deal_id

0
 
cdfllcAuthor Commented:
Thanks rafrancisco !

I don't know why, but I was thinking that the ids would have to be inserted into the rows at the same time.
But this works just fine!

thanks again!

cdfllc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.