CODEWITHSUNDEEP
sqldb2
Billy
Billy

Reputation: 45

Update table via a relation table

I have a table that record season score like score_table below. It records one subject three-season score S1, S2, and S3.

And sometimes one can give up some seasons score and makeup later by getting another subject same season score, so we have transfe_record table to save it.

So the question is how can I write back the score using transfe_record using SQL(DB2) code

Thanks!

score_table

|      key     |     subject   |     S1    |     S2    |     S3    |
+--------------+---------------+-----------+-----------+-----------|
|       1      |       A01     |     80    |    80     |     100   |
|       1      |       A02     |     78    |    98     |     null  |
|       1      |       A03     |     null  |    80     |     null  |
|       1      |       B01     |     null  |    null   |     null  |
|       1      |       B02     |     null  |    null   |     60    |
|       1      |       B03     |     66    |    null   |     null  |

transfe_record

|       key     |    org_sbj    |    trans_sbj   |
+---------------+---------------+----------------+
|        1      |      A02      |      B02       |
|        1      |      A03      |      B03       |

After SQL the score_table should be

|      key     |     subject   |     S1    |     S2    |     S3    |
+--------------+---------------+-----------+-----------+-----------|
|       1      |       A01     |     80    |    80     |     100   |
|       1      |       A02     |     78    |    98     |     60    |
|       1      |       A03     |     66    |    80     |     null  |
|       1      |       B01     |     null  |    null   |     null  |
|       1      |       B02     |     null  |    null   |     60    |
|       1      |       B03     |     66    |    null   |     null  |

Upvotes: 1

Views: 83

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12299

Try this:

merge into score_table a using
(
select t.key, t.org_sbj, b.S1, b.S2, b.S3
from transfe_record t
join score_table b on b.key=t.key and b.subject=t.trans_sbj
) m on m.key=a.key and m.org_sbj=a.subject  
and (a.S1 is null or a.S2 is null or a.S3 is null)
when matched then update set 
  S1=coalesce(a.S1, m.S1)
, S2=coalesce(a.S2, m.S2)
, S3=coalesce(a.S3, m.S3);

Select statement to insert into a new table:

select 
  a.key, a.subject
, coalesce(a.S1, b.S1) as S1
, coalesce(a.S2, b.S2) as S2
, coalesce(a.S3, b.S3) as S3
from score_table a
left join transfe_record t on t.key=a.key and t.org_sbj=a.subject
left join score_table b on b.key=t.key and b.subject=t.trans_sbj;

Upvotes: 2

Related Questions