質問: テーブル3.id_2 ○テーブル1 id_1 id_2 id_3 test test1 bbb 000001 001 000001 100 bbb 000002 001 000002 100 ○テーブル2 id_1 id_2 id_3 test test1 aaa 1 99 000001 100 ← このレコードを取り出したい aaa 2 99 000001 100 aaa 3 99 000003 100 aaa 4 99 000004 110 ○テーブル3 id_1 id_2 id_3 000001 001 aaa-1-99 ←id_3にテーブル2で取得できたid1 + id2 + id3を書き出したい 000002 001 夜遅くにすみませんがお願いします。
回答: update table3 set id_3=tx.id_3 from table3 t3 inner join (select t1.id_2 id_1,t1.id_3 id_2, (select top 1 id_1+'-'+convert(varchar,id_2)+'-'+convert(varchar,id_3) from table2 where test=t1.test and test1=t1.test1 order by id_1,id_2,id_3) id_3 from table1 t1) tx on tx.id_1=t3.id_1 and tx.id_2=t3.id_2 where tx.id_3 is not null なお、sql server 2005であればrow_numberも使えます。 いずれにせよ、必要なものを順番に組み立てることが肝要です。 update table3 set id_3=tx.id_3 from table3 t3 inner join (select t1.id_2 id_1, t1.id_3 id_2, t2.id_1+'-'+convert(varchar,t2.id_2)+'-'+convert(varchar,t2.id_3) id_3 from table1 t1 inner join (select row_number() over (partition by test,test1 order by id_1,id_2,id_3) seq, * from table2) t2 on t1.test=t2.test and t1.test1=t2.test1 where t2.seq=1) tx on tx.id_1=t3.id_1 and tx.id_2=t3.id_2