• ベストアンサー
  • 困ってます

SQL Server(MSDE2000) : ALTER TABLE

SQL Server(MSDE2000) : ALTER TABLE した項目に対し、直後に UPDATE で値をセットできない 既存のテーブルを仕様変更するため、列を追加し、初期値で埋める SQL 文を書こうとしていますが、うまく動かず、エラーとなってしまい、原因が判らず困っています。 サンプル SQL 文: ------ CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT UPDATE TEST_TABLE SET TEST_FIELD3 = 3 ------ ※テスト毎に、必ず DROP TABLE TEST_TABLE されている事が前提です。 上記 SQL 文のうち、1行目~3行目までを抜粋して実行すると、ちゃんと CREATE TABLE され、INSERT され、ALTER TABLE される事を確認しました。 ところが、4行目までを一気に実行しようとすると、 ------ SQL実行中に以下のエラーが発生しました。 エラーコード:207 [Microsoft][ODBC SQL Server Driver][SQL Server]列名 'TEST_FIELD3' は無効です。 SQLステータス:S0022 ------ となってしまい、UPDATE で初期値を埋める事ができません。 しかも、UPDATE に失敗するどころか、2行目の INSERT から以降が結果に反映されなくなるという状況に陥ってしまいます。 また、既存のテーブルの仕様変更が目的なので、その状況に近づけるために、まず、 ------ CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) ------ を実行し、既存のテーブル(とレコード内容)が存在する状態を作り出された事を、ツール等で確認してから、 ------ ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT UPDATE TEST_TABLE SET TEST_FIELD3 = 3 ------ の2行を実行してみると、やはり UPDATE は失敗し、前述と同じエラーが発生します。 またこの場合、ALTER TABLE の実行結果も反映されていません。(つまり、TEST_FIELD3 が列追加されていない) もちろん、ALTER TABLE だけを実行した場合には、ちゃんと列は追加されます。 その後に、UPDATE を実行すれば、ちゃんと追加列に初期値がセットされます。 どうやら、「一回の SQL 文の実行の中で、ALTER TABLE によって新設した列に対しては、UPDATE などでのアクセスはすぐにはできない」のではないか?という状況のようなのです。一回の SQL 文の実行の中において、何らかのトランザクション動作っぽい挙動を感じます。 つまり、ALTER TABLE で追加された列は、その時点ではまだ完全にシステムに認知されていないため、直後の UPDATE 文で認識できずに失敗するのではないか?と。そして、そこでのエラー発生が、ロールバック的に実行した処理をキャンセルしてしまうため、結果として、ALTER TABLE が実行されなかった事になったり、INSERT が実行されなかった事になったりしているのではないか?と思う次第です。 考えられる回避策としては、SQL 文を別々に作成し、個別に実行すれば良いだけの事なのですが、できれば、SQL 文一つにまとめたいと考えています。 どなたか、こういった現象に対する原因・理由の説明、或いは回避策など、何か情報をお持ちの方はいらっしゃいませんでしょうか? 宜しくお願いします。

共感・応援の気持ちを伝えよう!

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.1

SQL文を実行するときには、まずコンパイルして、実行プランを立て、それから実行するというステップがあるという点をご理解ください。 私の理解ではその時点でTEST_TABLEが存在しないかぎり、CREATE~INSERT~ALTER~UPDATEはエラーにならないはずです。 ただし、"既にTEST_TABLE(ALTER前)がある状態"で4行一緒に実行すると、 列名 'TEST_FIELD3' は無効です。 と返されますので、それと混同されているのではないかなと推測します。 (CREATE TABLE文は既にテーブルが存在していても、コンパイル時にはエラーにはならず、実行時にエラーになるため。 一方、UPDATE文は後述の通り、コンパイル時にエラーになります) CREATE~INSERTが成功したあとで、ALTER~UPDATEが失敗するのは、実行に失敗しているのではなく、コンパイルに失敗しているということです。 (「実行して失敗し、ロールバックされた」わけではありません) 既にそのテーブルが存在する場合、SQL Serverはコンパイル時にテーブルのカラムをチェックします。 この時点のテーブルにはTEST_FIELD3はありませんので、コンパイルエラーになります。 一方、実行時にそのテーブルが存在しない場合は、遅延解決と言ってコンパイル時にはカラムがチェックされません。 (実行時に条件がそろっていなければエラーとなります) 従って、CREATE~INSERT~ALTER~UPDATEと一気に実行すると、チェックすべきテーブルがまだ存在していないため、エラーにはなりません。 もう一つ注意すべきなのは、キャッシュの存在です。 一度「UPDATE TEST_TABLE SET TEST_FIELD3 = 3」が成功すると、このクエリはプランキャッシュというところにキャッシュされ、必要に応じて再利用されます。 その状態でテーブルをDROPし、CREATE~INSERTを行い、ALTER~UPDATEを行うと、キャッシュされた情報を参照してTEST_FIELD3は存在すると判断され、コンパイルが通りますので、今度はエラーになりません。 ご参考までに。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

早速の回答、ありがとうございました。 頂いた回答の中の「コンパイル→実行プラン立て→実行」というステップのお話からヒントを得て、問題を解決する方法が見つかりました。 まず、プランキャッシュの存在の事が全く頭に無かったため、UPDATE を実行する時点で、オプティマイザがまだ「TEST_FIELD3」の存在に気が付いていないため(直前で ALTER TABLE で追加される事なんて、コンパイル時点でオプティマイザが知る由もありませんよね)、当たり前のようにエラーになる事に気が付いていませんでした。 しかも、完全に DROP TABLE した状態からでは、提示した4行の実行ではエラーが発生せず、コンパイルが通ってしまう事も、当方のテスト不足で、気が付いていませんでした。 ご指摘を頂いたポイントを参考にし、解決方法を模索した結果、以下のような方法で、当方の問題が解決できた事をご報告します。 まず、 -------- CREATE TABLE TEST_TABLE ( TEST_FIELD1 SMALLINT, TEST_FIELD2 SMALLINT) INSERT INTO TEST_TABLE VALUES( 1, 2 ) -------- を先に実行しておいた状態で、 -------- DECLARE @SQLSTR VARCHAR(64) SET @SQLSTR = 'UPDATE TEST_TABLE SET TEST_FIELD3 = 3' ALTER TABLE TEST_TABLE ADD TEST_FIELD3 SMALLINT EXEC (@SQLSTR) -------- というSQL文を実行します。 もうお気付きかと思いますが、コンパイル時にエラーとなってしまっていた UPDATE 文を、文中で動的に組み立てた事にしてしまい、EXECで実行した訳です。 「動的に組み立てられたSQLを実行する際は、実行時にコンパイルされる」という特性を利用してみました。 これによって、当方の問題を解決する事ができました。 ご回答頂いた内容が、かなりのヒントになりました。非常に助かりました。 それと、今回の指摘を受けて思った事は、プランキャッシュの影響を受けるようなSQL文をデバッグする際には、実行毎に、SQLServerのサービスを停止・再開させて強制的にプランキャッシュを破棄した方が、実行結果がプランキャッシュの影響を受けずに済むという点です。ここについては、今後も注意したいと思います。 当然ながら、ベストアンサーとさせて頂きます。 ありがとうございました!

関連するQ&A

  • SQLServer2000:SQL文の記述について

    いつもお世話になっております。 SQLServer2000でSQL文で素朴な疑問なのですが、 質問が3点ございます。 1:列の追加 ALTER TABLE 文で、フィールドを追加する際に、 "]"(かぎ括弧閉じ) がフィールド名に存在する場合、どのように記述すればよいのでしょうか。 2:レコードの追加 INSERT 文で追加するレコードの文字列中に "'" (シングルクオテーション) が値に存在する場合、どのように記述すればよいのでしょうか。 3:列の削除 ALTER TABLE でフィールドを削除する際に、 "#"(シャープ) がフィールド名の先頭に存在する場合、どのように記述すればよいのでしょうか。 ご存知の方がいらっしゃいましたら、 ご回答をよろしくお願いいたします。

  • SQLでフィールドの順番を変更したい

    お世話になっております。 ORACLEを使用しております。 既存のテーブルに新しいフィールドをSQL文にて ALTER TABLE TEST_MST ADD TEST_CD VARCHAR2(2); と言う風に、SQL*PLUSにて追加いたしました。 これを参照しますと、 追加したフィールドがテーブルの一番最後のフィールドに 追加されているのですが、 フィールドの順番を変更したいと考えております。 SQL文にて何か方法はございませんでしょうか。 ご返答を宜しくお願い致します。

  • Oracle テーブルの列削除

    タイトルの通りテーブル(test)の列(retu)を削除したいのですが・・・ Sql*pLUS画面 SQL> alter table test drop(retu); ←この用に入力 alter table test drop(retu) * エラー行: 1: エラーが発生しました。 ORA-00905: キーワードがありません。 となってしまい列を削除できません。 なにか構文の間違いでしょうか?

  • alter tableすると、処理が止まってしまい困っています

    表題の件で質問させて下さい。 以前までは特に問題なく、alter tableなどで列を追加出来ていたのですが、ここ最近、データ量が増えてきたためか、列追加にすごく時間がかかってしまっています。 それが原因なのかはわからないのですが、alter tableで列を追加すると、処理が止まってしまい、プロセスをkillして、とりあえず対処する・・・ と言うような対応が続いています。 調べたところ、alter table はテーブルロックがかかってしまうらしいのですが、これは begin でトランザクションを開始させても特に意味はないのでしょうか? いろいろと調べてはいるのですが、基本的な事は見つかるのですが、運用する時にどういった注意点があるか等が今いち、調べ切れませんでしたので、どんな運用をしていけばいいのかご教授して頂けると助かります。 宜しくお願いいたします。

  • mysqlのalter table中のロックについてです。

    mysqlのalter table中のロックについてです。 下記のように、alterでテーブルを再構築中に同じテーブルにinsertが実行された場合、 接続Bのinsertはブロックされるかと思うのですが、テーブルが大きくalterに時間がかかる場合、 タイムアウトなどは発生するのでしょうか。 またもし発生する場合、タイムアウト値の設定などの確認方法はあるのでしょうか。 1.接続A  alter table table1 add columnB int(11) ; 2.接続B(接続Aのalter実行中)  insert into table1(columnA) values('aaa');

    • ベストアンサー
    • MySQL
  • フィールドの削除でつまずいています。

    フィールドの削除でつまずいています。 Aマシン VineLinux Bマシン RedHat9 同じデータを使っています。データベースもPostgreSQLです。 Aで、alter table test_table drop column test_field;でエラーが発生し Bでは何のエラーも無く実行できました。 考えられる原因は何でしょうか? ちなみに、 update test_table set test_field = null;としています。 SQLの知識が乏しいので、基礎的なことを忘れているかもしれません。

  • MSDEのSQLについて

    DB初心者です。 通常のAcceessで支障なく使っていた以下のようなSQL文がMSDEのビューではエラーがかかって使えません。原因と対応法を教えて頂けないでしょうか? < 「テーブル1」の「フィールド1」と「フィールド2」の相関係数を算出するSQL文 > SELECT (Sum(([フィールド1]-(SELECT AVG([フィールド1]) FROM テーブル1))*([フィールド2]-(SELECT AVG([フィールド2]) FROM テーブル1))))/Sqr(Sum(([フィールド1]-(SELECT AVG([フィールド1]) FROM テーブル1))^2)*Sum(([フィールド2]-(SELECT AVG([フィールド2]) FROM テーブル1))^2)) AS 相関係数 FROM テーブル1; これがMSDEだと下のようなエラーになります。 ADOエラー: 集計やサブクエリを含む式に対して集計関数を実行することは出来ません。 どうしたいいでしょうか? また、MSDEや易しいTransactSQLを習得する良い方法があればアドバイスを頂きたいのですが。

  • Access2003_ALTER TABLE構文

    Accessのテーブルにフィールドを追加したくSQLで命令文を書いています。 1.[T_テーブル1]というテーブルに[フィールドA]というフィールドを データ型:数値型 フィールドサイズ:単精度浮動小数点型 小数点以下表示桁数:2位まで表示 で追加したいです。 →ALTER TABLE T_テーブル1 ADD COLUMN フィールドA FLOAT4 これに少数点以下桁数を設定するにはどう書けばよろしいでしょうか? 2.[T_テーブル1]というテーブルに[フィールドB]というフィールドを データ型:数値型 フィールドサイズ:長整数型 で追加しました。 →ALTER TABLE T_テーブル1 ADD COLUMN フィールドA INT 「説明」の箇所に説明書きも入れたいのですがここでの設定は可能でしょうか? 以上2点につきましてご指導頂きたくよろしくお願い致します。

  • SQLServer:SQL文でシングルクオテーションを混在させたフィールドを追加したい

    SQLServer2000を使用しております。 タイトルのとおりなのですが、 SQL文でシングルクオテーションを混在させたフィールドを追加するにはどのように記述すればよいのでしょうか。 レコードに混在させるには2つシングルクオテーションを記述すればよいというのはわかったのですが、 ALTER TABLE 等でフィールドを追加するときは、 この記述では追加が行われませんでした。 どなたかご教授の程、宜しくお願い致します。

  • Access2000におけるテーブル追加について

    テーブル1にテーブル2の内容をすべて追加しようと思います。 二つのテーブルのフィールド項目は同じにしました。(テストなのでとりあえず番号と名前) マクロを使ってやろうと思い、アクションを「SQLの実行」とし、SQLステートメント には「INSERT INTO <テーブル1> SELECT<社員番号>,<名前>FROM<テーブル2>」 としました。 しかし、マクロを実行してみると「INSERT INTO 構文エラー」となってしまいます。 なぜなんでしょうか?