- PR -

sql-serverで連番に一括更新するsqlについて

1
投稿者投稿内容
ちゃむ
会議室デビュー日: 2005/01/14
投稿数: 11
投稿日時: 2006-05-15 16:21
まだSQL初心者ですが、宜しくお願いします。

テーブル：m_table

No SubNo Area Name
---- ---- ------ ------
1001 14 北海道 山田
2001 22 東北 田中
2002 58 東北 長田
8003 39 関西 佐藤
9001 16 九州 鈴木

このテーブル例で登録済みの SubNo を一括で連番に更新することは出来ますか。

一通り調べてみて、Oracleでは以下のような記述で一括処理できるようですが
SQL-Server ではそのまま使えないようです。。。

update m_table a
set SubNo = (select count(b.No)+1 from m_table b where b.No < a.No);

また、実際には
Areaが北海道の場合は SunNO=1001〜
Areaが東北の場合は 　SunNO=2001〜
のように連番処理の対象を指定するのが目的です。

どなたか良い方法をご存じでしたら、
よろしくお願いします！

今川 美保(夏椰)
ぬし
会議室デビュー日: 2004/06/10
投稿数: 363
お住まい・勤務地: 神奈川県茅ヶ崎市
投稿日時: 2006-05-15 18:29
SQLServerのバージョンは何でしょうか？
#2000? 2005?

2005から増えたROW_NUMBER関数を使うと
以下のようなSQLで対応できたりするんですが･･･。
コード:
update NoTest
Set SubNo = NewNo
from NoTest,
( select 
	cast(  row_number() over ( partition by NO order by subNo ) AS char) NewNo ,No,SubNo
 from NoTest
) bufT
WHERE NoTest.No = bufT.No AND
NoTest.subNo = bufT.subNo
;


#実行前
コード:
No   | subNo | val
-------------------
1001 | 90    | 9         
1001 | 91    | 8         
1001 | 92    | 7         
1001 | 93    | 6         
1001 | 94    | 5         
1001 | 95    | 4         
1001 | 96    | 3         
1001 | 97    | 2         
1001 | 98    | 1         
1001 | 99    | 0         
1002 | 80    | 9         
1002 | 81    | 8         
1002 | 82    | 7         
1002 | 83    | 6         
1002 | 84    | 5         
1002 | 85    | 4         
1002 | 86    | 3         
1002 | 87    | 2         
1002 | 88    | 1         
1002 | 89    | 0         
1003 | 70    | 9         
1003 | 71    | 8         
1003 | 72    | 7         
1003 | 73    | 6         
1003 | 74    | 5         
1003 | 75    | 4         
1003 | 76    | 3         
1003 | 77    | 2         
1003 | 78    | 1         
1003 | 79    | 0


#実行後
コード:
No   | subNo | val
-------------------
1001 | 1     | 9         
1001 | 2     | 8         
1001 | 3     | 7         
1001 | 4     | 6         
1001 | 5     | 5         
1001 | 6     | 4         
1001 | 7     | 3         
1001 | 8     | 2         
1001 | 9     | 1         
1001 | 10    | 0         
1002 | 1     | 9         
1002 | 2     | 8         
1002 | 3     | 7         
1002 | 4     | 6         
1002 | 5     | 5         
1002 | 6     | 4         
1002 | 7     | 3         
1002 | 8     | 2         
1002 | 9     | 1         
1002 | 10    | 0         
1003 | 1     | 9         
1003 | 2     | 8         
1003 | 3     | 7         
1003 | 4     | 6         
1003 | 5     | 5         
1003 | 6     | 4         
1003 | 7     | 3         
1003 | 8     | 2         
1003 | 9     | 1         
1003 | 10    | 0
ちゃむ
会議室デビュー日: 2005/01/14
投稿数: 11
投稿日時: 2006-05-15 19:19
回答ありがとうございます。

残念ながらSQL-Server2000です。
現バージョンだと簡単な記述は難しいのですかね。。

LOOP処理を行えば出来なくはないのですが、
せっかくなので他に良い手が無いか検討しているところです。

よろしくお願いします。

unibon
ぬし
会議室デビュー日: 2002/08/22
投稿数: 1532
お住まい・勤務地: 美人谷　　　　　　　　良回答(20pt)
投稿日時: 2006-05-15 20:22
引用:

ちゃむさんの書き込み (2006-05-15 16:21) より:
一通り調べてみて、Oracleでは以下のような記述で一括処理できるようですが
SQL-Server ではそのまま使えないようです。。。

update m_table a
set SubNo = (select count(b.No)+1 from m_table b where b.No < a.No);

これは「自己結合」というやりかたですが、SQL Server でもこれと同じようなやりかたでできませんか？
あいにく手近に試す環境がないのですが、できてもよさそうな気がするのですが。
＃フィールド名が No なのは、なんとなく予約語とぶつかりそうな気もしますが。

ちなみに、自己結合は対象となるレコード数の2乗に比例した処理が必要なので遅いですが、それさえ我慢すれば確実な方法です。もっとも、自己結合が必要な処理と言うのは RDB の使い方から逸れることが多いので、データー構造を再検討したほうが良いこともあります。

--
unibon {B73D0144-CD2A-11DA-8E06-0050DA15BC86}
ちゃむ
会議室デビュー日: 2005/01/14
投稿数: 11
投稿日時: 2006-05-16 10:03
クエリアナライザーで動作確認していますが

update m_table a
set SubNo = (select count(b.No)+1 from m_table b where b.No < a.No);

サーバー : メッセージ 170、レベル 15、状態 1、行 1
行 1: 'a' の近くに無効な構文があります。
サーバー : メッセージ 156、レベル 15、状態 1、行 2
キーワード 'where' 付近に正しくない構文があります。

とエラーになります。。。

まだSQLでもサブクエリや複雑な結合処理を完全に理解していないので
いろいろと試しながら確認しているところです。

確かに自己結合だと記述上は一括でも内部的には時間がかかりそうなので
今回は複数行に記述して処理してみようと思います。

かめたろ
ぬし
会議室デビュー日: 2003/03/20
投稿数: 255
投稿日時: 2006-05-16 14:50
構文エラーと出てますので・・・
コード:
update a set
    SubNo = (select count(b.No)+1 from m_table b where b.No < a.No)
from m_table a
1

スキルアップ／キャリアアップ（JOB@IT）