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ベストアンサー
- 回答日時:
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様ご回答ありがとうございます。実際の処理では1~2万件のデータがあり、元データは変更しないで一度テンポラリテーブルに移してから変更処理をするのでINDEXを有効につけることによりパフォーマンスは上がりそうですね。参考にさせていただきたいと思います。ありがとうございます。
No.3
- 回答日時:
#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
No.2
- 回答日時:
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
- 回答日時:
非常にかっこ悪いですが、ここまでやれば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さん、ご回答ありがとうございます。やればなんとか出来るのですね。SQLの奥深さに感心させられます。考え方のひとつとして参考にさせていただきます。本当にありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(プログラミング・Web制作) google formsを使ったタスク依頼フォーム作成におけるご相談 1 2023/06/22 15:55
- Oracle SQL update方法 2 2022/06/22 14:07
- その他(データベース) 更新クエリをリンクデータベーステーブルに実行し実行時エラー3362固有インデックスに重複する値が含ま 1 2022/09/21 11:44
- Access(アクセス) アクセス テーブルの空白を変数に置換するボタンが作りたい 4 2022/07/08 11:19
- その他(データベース) accessについて 2 2022/05/31 16:58
- SQL Server SQL 期間内の実施数を合計したい 2 2023/03/03 22:28
- Oracle sqlで質問です。 Aテーブルの情報をBテーブルに更新かけたいです。 やりたいことは、Bテーブルの受 1 2023/05/17 11:17
- Ruby No route matches [GET] "/posts/5/destroy" 1 2022/03/24 13:00
- Oracle sqlのupdate文で質問です。 テーブルBの番号をキーにテーブルAの身長をテーブルBの身長に更新 2 2022/11/02 15:15
- Windows 10 BitLocker 回復キーの入力 1 2022/10/09 15:23
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
マイクラPC版のコマンドで効率...
-
副問合せの書き方について
-
クエリ表示と、ADOで抽出したレ...
-
VIEWの元のテーブルのindexって...
-
[MySQL] 3つのテーブルの結合で...
-
エラー 1068 (42000): 複数の主...
-
SQLサーバから、項目の属性(型...
-
エクセルの関数について教えて...
-
PL/SQLの変数について
-
SQL Left Join で重複を排除す...
-
WordpressのContact form 7でzi...
-
URL と行番号の指定
-
1テーブル&複数レコードの更新...
-
Postgreのupdateコマンドでエラー
-
テーブル名を省略して「h.id」...
-
Access パラメータクエリをcsv...
-
sqlで、600行あるテーブルを100...
-
WHERE id = ? について
-
存在しないレコードの抽出方法...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルで最後の文字だけ置き...
-
SQL Left Join で重複を排除す...
-
SQLサーバから、項目の属性(型...
-
副問合せの書き方について
-
VIEWの元のテーブルのindexって...
-
エクセルの関数について教えて...
-
SQLにて特定の文字を除いた検索...
-
マイクラPC版のコマンドで効率...
-
select文のwhere句に配列を入れ...
-
sqlで、600行あるテーブルを100...
-
ある条件の最大値+1を初番する...
-
inner joinをすると数がおかし...
-
Unionした最後にGROUP BYを追加...
-
期間の重複を調べるSQL文につい...
-
クエリ表示と、ADOで抽出したレ...
-
Access パラメータクエリをcsv...
-
PL/SQLの変数について
-
MySQLのDATE型カラム値がNULLの...
-
php+mysqlで複数選択削除について
-
上位3位を求めるSQL文は?
おすすめ情報