最小値をUPDATE

質問

UPDATE文で効率のよい方法を模索しています。皆様方のお知恵をお貸しいただければと思い投稿してみました。

以下のようなテーブルが存在していると仮定します。
TB_1
id|custom|tanto1|tanto2|syohin|date
1 |1 | 10| 10| 999|2007/01/01
2 |1 | 10| 10| 999|2007/02/01
3 |1 | 20| 20| 999|2007/03/01
4 |1 | 20| 20| 999|2007/04/01
5 |1 | 10| 10| 777|2007/04/01
※idはプライマリキーです。
※date以外はすべて数値となっております。

custom,syohinが同じものでtanto1に複数の値が存在する場合、一番古いdate（もし同dateが存在するならidの小さい方）のtanto1をtanto2に書き換えたい。

※上記の例ではid 3と4をid 1のtanto1をtanto2へ更新します。

↓

更新結果
id|custom|tanto1|tanto2|syohin|date
1 |1 | 10| 10| 999|2007/01/01
2 |1 | 10| 10| 999|2007/02/01
3 |1 | 20| 10| 999|2007/03/01
4 |1 | 20| 10| 999|2007/04/01
5 |1 | 10| 10| 777|2007/05/01

UPDATEでEXISTS句を使ったり、サブクエリで試してみましたが思った結果が得られなかったので、
とりあえずストアドプロシージャでカーソルを使って更新する処理をしたのですが、（処理をここで説明するのは省略させていただきます）

UPDATE文を駆使して、できないものでしょうか。

SQL2000およびSQL2005のどちらでも動作することが条件となります。

誠に恐れ入りますがご教授願います。

回答No.4

nora1962 · ベストアンサー

CREATE TABLE TBL
(
ID INT PRIMARY KEY,
CUSTOM INT,
TANTO1 INT,
TANTO2 INT,
SYOHIN INT,
DATE DATETIME
);

CREATE INDEX TBL_IDX1 ON TBL ( CUSTOM, SYOHIN, DATE );
CREATE INDEX TBL_IDX2 ON TBL ( CUSTOM, SYOHIN, TANTO1 );

INSERT INTO TBL VALUES (1 ,1 , 10, 10, 999,'2007/01/01' );
INSERT INTO TBL VALUES (2 ,1 , 10, 10, 999,'2007/02/01' );
INSERT INTO TBL VALUES (3 ,1 , 20, 20, 999,'2007/03/01' );
INSERT INTO TBL VALUES (4 ,1 , 20, 20, 999,'2007/04/01' );
INSERT INTO TBL VALUES (5 ,1 , 10, 10, 777,'2007/05/01' );

UPDATE T1
SET T1.TANTO2 =
( SELECT TOP 1 T2.TANTO1
FROM TBL T2
WHERE T1.CUSTOM=T2.CUSTOM
AND T1.SYOHIN=T2.SYOHIN
ORDER BY T2.DATE , T2.ID )
FROM TBL T1
WHERE EXISTS
( SELECT 1 FROM TBL T3
WHERE T1.TANTO1<>T3.TANTO1
AND T1.CUSTOM=T3.CUSTOM
AND T1.SYOHIN=T3.SYOHIN )

かなり冗長なインデックスですが、ここまでしなくても、(CUSTOM,SYOHIN)
にインデックスを貼れば、かなりパフォーマンスは違ってくると思いま
す。

お礼

nora1962様ご回答ありがとうございます。実際の処理では１～２万件のデータがあり、元データは変更しないで一度テンポラリテーブルに移してから変更処理をするのでINDEXを有効につけることによりパフォーマンスは上がりそうですね。参考にさせていただきたいと思います。ありがとうございます。

回答No.3

chukenkenkou

#2です。
idが日付順と、勝手に解釈してしまったようです。

＜改善版SQL例＞
update TB_1
set tanto2=ZZ.tanto1
from
(select Y.id as id,Y.custom as custom,Y.syohin as syohin,XX.tanto1 as tanto1
from
(select * from TB_1) as Y,
(select * from TB_1 as X
where id=(select top 1 id
from TB_1
where X.custom=custom and X.syohin=syohin
order by custom,syohin,date,id)
) as XX,
(select custom,syohin
from TB_1
group by custom,syohin
having count(distinct tanto1)>1) as Z
where XX.custom=Y.custom and XX.syohin=Y.syohin
and Y.custom=Z.custom and Y.syohin=Z.syohin
) as ZZ
where TB_1.id=ZZ.id

お礼

chukenkenkou様わざわざ改善していただきありがとうございます。

回答No.2

chukenkenkou

SQL Server 2005では実機確認したのですが、こんな感じでどうでしょう？

update TB_1
set tanto2=(select tanto1 from TB_1 where id=Y.id)
from TB_1 as X,
(select custom,syohin,min(id) as id from TB_1
group by custom,syohin) as Y
where X.custom=Y.custom and X.syohin=Y.syohin
and X.id>Y.id

お礼

chukenkenkou様ご回答ありがとうございます。とても興味深いSQL文です。ただ質問内にあるように「一番古いdate・・」という条件があるため、これをヒントに自身で考えてみたいと思います。
どうもありがとうございました。

回答No.1

aoman

非常にかっこ悪いですが、ここまでやればUPDATEのみで出来ますよ。
正し、レスポンス等は一切考慮しておりませんのでご注意を。

update tb1
set tanto2 = e.tanto1
from
(
select tb1.custom,tb1.syohin,tb1.tanto1
from tb1,
(
select tb1.custom,tb1.syohin,min(tb1.id) as id
from tb1,
(
select tb1.custom,tb1.syohin, min(tb1.date) as date
from tb1,
(
select custom,syohin
from
(
select custom,syohin,tanto1
from tb1
group by custom,syohin,tanto1
) a
group by custom,syohin
having count(*) > 1
) b
where tb1.custom = b.custom
and tb1.syohin = b.syohin
group by tb1.custom,tb1.syohin
) c
where tb1.custom = c.custom
and tb1.syohin = c.syohin
and tb1.date = c.date
group by tb1.custom,tb1.syohin
) d
where tb1.custom = d.custom
and tb1.syohin = d.syohin
and tb1.id = d.id
) e
where tb1.custom = e.custom
and tb1.syohin = e.syohin

どうでしょう？

お礼

aomanさん、ご回答ありがとうございます。やればなんとか出来るのですね。ＳＱＬの奥深さに感心させられます。考え方のひとつとして参考にさせていただきます。本当にありがとうございました。