Self join question

[AlStl]

I am new to SQL and oracle and here is my dilemma:

I have a table called TMP_YR (described below)

SN YEAR KEY RCPT NWCI

XX1004 2004 X 0 12
XX1004 2005 X 1 12
YY1004 2004 X 0 25
YY1004 2005 X 1 29
XX1004 2004 Y 0 4
XX1004 2005 Y 1 8
YY1004 2004 Y 0 6
YY1004 2005 Y 1 100
KK1004 2004 Y 0 6
KK1004 2005 Y 1 100


SN,YEAR, KEY,RCPT fields make up the primary key of TMP_YR table.

I have another table TMP_SN:

SN KEY REGION

XX1004 X SW
YY1004 Y MW
KK1004 X W


SN and KEY fields make up the primary key of TMP_SN table. Also they are foreign keys in TMP_YR table.

I would like TMP_YR table column NWCI where KEY = 'X' to be copied to NWCI where KEY = 'Y'

For an individual region (I guess I need to do a join between TMP_YR and TMP_SN to get the region). So a self join and a join between these two table should do the trick.

I am going to run this job via cron for different regions at different times.

Any Suggestions?

Al

 

[carp]

If I understand your requirements, then I think

UPDATE tmp_yr o
SET NWCI = (SELECT nwci
              FROM tmp_yr i
             WHERE i.key = 'X'
               AND REPLACE(i.sn,'X','Y') = o.sn
               AND i.year = o.year
               AND i.rcpt = o.rcpt)
WHERE o.key = 'Y';

should do it.

If this is not what you want, then it can at least serve as a starting point towards a solution for you.