人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

SQL文について質問です。

あるテーブルを元にして変更を加えた新テーブルを作成し、
元テーブルからデータをコピーします。
データコピーの際に、新テーブルで新たに追加した列へ
自動採番を行った値をinsertして行きたいのですが、方法がわかりません。

データは1件ずつではなく、一度の処理でコピーしたいです。
詳細手順は下記に示します。


CREATE TABLE hoge (hoge text);

上記のようなテーブルがあったとします。
このhogeテーブルを、hoge_idと言う列を持ったテーブルに変更します。
変更方法は下記の通りとします。

1.今あるhogeテーブル名をbk_hogeに変更
2.hoge_id列の定義を追加した新hogeテーブルを作成
CREATE TABLE hoge
(
hoge_id numeric(2),
hoge text,
CONSTRAINT hoge_pkey PRIMARY KEY (hoge_id)
);

3.bk_hogeに既にinsertされているデータを、新hogeテーブルにコピー
INSERT INTO hoge SELECT
★,
bk_hoge.hoge
FROM bk_hoge;

この★の部分で、データ1件ずつに対して自動採番を行った値を入れていきたいです。

よい方法がございましたらご教授願えれば幸いです。
よろしくお願い致します。

●質問者: yuri-jda
●カテゴリ:コンピュータ
✍キーワード:hoge Key SELECT SQL コピー
○ 状態 :終了
└ 回答数 : 5/5件

▽最新の回答へ

1 ● TONTON3
●11ポイント

http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj01.htm

oracleであればsequence というのが使えますが対象は何になりますか?

◎質問者からの返答

記載漏れとなり申し訳ございません。

使用環境はPostgresです。


2 ● きゃづみぃ
●11ポイント

オラクルでしたら ROWNUMを 入れたらいいでしょう。



http://q.hatena.ne.jp/1192075764

◎質問者からの返答

INSERT INTO hoge SELECT

ROWNUM,

bk_hoge.hoge

FROM bk_hoge;

上記を実行しましたが、環境がPostgresの為か

ERROR: column "rownum" does not exist

が発生してしまいました。

PostgresにはROWNUMが無いようですね。残念です。


3 ● Mook
●50ポイント ベストアンサー

使用しているDatabase は明記された方が良いと思います。


いろいろやり方はあると思いますが、PostgreSQL であればプロシージャを使用したやり方はどうでしょうか。

以下 SQL として実行します。



【コピー先テーブルの作成】

 CREATE TABLE bk_hoge (
 hoge_id NUMERIC(2) PRIMARY KEY,
 hoge TEXT );

【プロシージャの作成】

CREATE OR REPLACE FUNCTION copy_table() RETURNS INTEGER AS $$
DECLARE
 mv RECORD;
 num INTEGER;
BEGIN
 num := 0;
 FOR mv IN SELECT * FROM hoge ORDER BY hoge LOOP
 num := num + 1;
 INSERT INTO bk_hoge ( hoge_id, hoge ) values ( num, ''||mv.hoge||'' );
 END LOOP;
 RETURN num;
END;
$$ LANGUAGE 'plpgsql'

【プロシージャの実行】

select copy_table();

copy_table は新規テーブルに対して実行するのが前提ですので、再実行する前にテーブルを再作成してください。

(プロシージャの中でやっても良いのですが、あえて外に出しました。)


http://www.postgresql.jp/document/pg824doc/html/plpgsql.html

http://www50.tok2.com/home/oppama/pgfuncdef.html

不明な点はコメントで対応します。

◎質問者からの返答

詳細な解説をありがとうございました。

「プロシージャ」というものを使用したことが無いので、

記載していただいた【プロシージャの作成】について解析させて頂きます。

見た事のない構文もあるので、質問させていただくこともあるかもしれませんが、

その際はどうぞよろしくお願い致します。

ありがとうございました。


4 ● KUROX
●11ポイント

http://homepage2.nifty.com/lightbox/VA003334/dbaccess05090717281...

http://www.itmedia.co.jp/enterprise/0307/18/epn20_6.html

Postgresでも、sequence使えるようですよ。

sequenceを作成するのが手堅いような。

◎質問者からの返答

自動採番について、色々なDB条件で記載されていて

今回以外にも参考にさせて頂けそうです。

ありがとうございました。


5 ● chuken_kenkou
●17ポイント

PostgreSQLでの自動採番は、連番型(SERIAL型)を使います。

CREATE SEQUENCEも実装されており、SERIAL型は等価です。

1.テーブル名の変更

-- リネーム
ALTER TABLE hoge
 RENAME TO bk_hoge;

2.新テーブルの作成

2.1 新テーブルの定義

-- 新テーブル
CREATE TABLE hoge
(hoge_id SERIAL, -- PostgreSQLでの連番型。CREATE SEQUENCEも使用可能
 hoge TEXT,
 CONSTRAINT hoge_pkey PRIMARY KEY (hoge_id)
);

連番型を操作する場合、「シーケンスの名前」を指定する必要があります。CREATE SEQUENCEを使う場合は、名前はユーザ指定になりますが、SERIAL型で定義時は、「表名_列名_seq」になります。

上記の表定義なら、「hoge_hoge_id_seq」という名前で生成されます。

2.2 データのコピー

(1)連番の初期化

連番型を持つ表を定義した直後なら、初期化は自動的に行われます。

テスト等でリセットしたい場合は、次のような操作を行います。

TRUNCATE TABLE hoge;
SELECT SETVAL('hoge_hoge_id_seq',1,FALSE);

(2)データのコピー<指定例1>

連番型の列を除いてINSERTする。

INSERT INTO hoge(hoge) -- 連番型以外の列を指定
 SELECT hoge FROM bk_hoge;

(3)データのコピー<指定例2>

NEXTVAL関数を使う。

INSERT INTO hoge
 SELECT
 NEXTVAL('hoge_hoge_id_seq'),
 hoge
 FROM bk_hoge;

◎質問者からの返答

詳細な解説をありがとうございました。

但し、今回の場合は列自体に自動採番の制約をかけたくなかったので

教えて頂いた方法は、別の機会に参考にさせて頂きます。

ひとつ気になったのですが、

(2)データのコピー<指定例1>

連番型の列を除いてINSERTする。

では、hoge_idがPkeyとなっているので

他の列の値を設定しようとするとエラーが出ないでしょうか・・・?

似たような方法を試したのですが、エラーが出てしまいました。

SERIAL制約ではないテーブルでしたし、

私の文法が違ったのかもしれませんが。。。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ