Home > IT Answers > DataManagement > Updating one record with a field of another r...
Help

Question

  Asked: Mar 1 2007   3:53 PM GMT
  Asked by: donnewbee

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


Data analysis, Database, Oracle, DB2, 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!

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
Last Answered: Mar 1 2007  4:52 PM GMT  by SheldonLinker




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)
  • AddThis Social Bookmark Button

Browse more Questions and Answers on DataManagement, Database and Oracle.

Looking for relevant DataManagement Whitepapers? Visit the SearchDataManagement.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

donnewbee  |   Mar 2 2007  12:02PM GMT

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