?
Solved

Tricky SQL

Posted on 2005-05-09
3
Medium Priority
?
217 Views
Last Modified: 2010-03-19
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


0
Comment
Question by:cdfllc
3 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13962817
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
 
LVL 7

Expert Comment

by:rohanbairat3
ID: 13963497
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
 
LVL 1

Author Comment

by:cdfllc
ID: 13963623
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question