Updating one record with a field of another record where two other fields match

pts.
Tags:
Data analysis
Database
DB2
Oracle
SQL
I have the following Table: XREF Group Projid id1 id2 ----- -------- ----- ----------- AEEC SOCAL 71 442760 CAG AWPE 71 442780 CAG MONDPL 71 447585 CAG PLANDI 71 442780 CAG SMCECA 71 442780 CAG AWPE 84 442781 CAG MONDPL 84 447588 AEEC MONDP 84 447589 CAG PLANDI 98 288923 CAG SMCECA 98 288923 CAG MONDPL 98 288923 CAG SMCECA 99 420109 CAG MONDPL 99 420109 CAG PLANDI 99 420109 CAG PLANDI 112 423445 CAG MONDPL 112 423445 CAG PLANDI 114 288665 CAG MONDPL 114 288665 CAG AWPE 115 442784 CAG MONDPL 115 447596 CAG AWPE 153 442785 CAG MONDPL 153 447613 CAG AWPE 154 442786 CAG MONDPL 154 447614 I need to update field id2 of the records that have Projid = MONDPL and also have id1 = id1 of record with Projid = AWPE with the id2 of record with Projid = AWPE. for example CAG AWPE 71 442780 CAG MONDPL 71 447585 CAG PLANDI 71 442780 CAG SMCECA 71 442780 would become CAG AWPE 71 442780 CAG MONDPL 71 447580 CAG PLANDI 71 442780 CAG SMCECA 71 442780 What would be the best approach? Any sql code will be very helpful. THANKS!

Answer Wiki

Thanks. We'll let you know when a new response is added.

In DB2 or Oracle, use:

UPDATE xref a
___SET id1 = (
______SELECT b.id2
______FROM xref b
______WHERE b.projId=’AWPE’ AND
_________a.id1=b.id1)
___WHERE projId=’MONDPL’;

In MS SQL Server, add AS after xref.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linker.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Donnewbee
    Thanks so much for the reply! Great idea! I was making it a lot harder than it should be! I changed some items to do what I want it to do. May not have done it correctly. Please check UPDATE xref a SET a.id2 = ( SELECT b.id2 FROM xref b WHERE b.projid='AWPE' AND a.id1= b.id1AND a.id2 b.id2) WHERE a.projid='MONDPL'; I want to change a.id2 where a.projId='MONDPL' to b.id2 where b.projId='AWPE' and a.id1 equals b.id1 and a.id2 is NOT equal to b.id2 (guess this last condition really doesn't matter - just saves an update) for example Grp Projid id1 id2 ----- -------- ----- ----------- CAG MONDPL 71 447585
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following