0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

mysqlで、テーブルからselectしたデータの一部を変更してそのままINSERTするクエリの書き方

Last updated at Posted at 2019-06-28

[mysqlで、テーブルからselectしたデータの一部を変更してそのままINSERTする方法]

テーブル:table_1
カラム:acct_id,column_a,column_b,column_c,create_date,update_date

qiita.rb
INSERT INTO table_1  (
SELECT 
	'acctid_1234567890',
        column_a,
        column_b,
        column_c,
        NOW(),
        NOW()
        from table_1  
 WHERE
        acct_id = 'acctid_0000000000');

コピー元のデータのselect条件⇒(acct_id = 'acctid_0000000000')
acct_idを任意の新しいid(acctid_1234567890)、create_date,update_dateをシステム日時にする


※keyを連番にして同じデータを大量に複製するとき

acct_id : acctid_0000000000 ~ acctid_0000099999 を作成

qiita.rb

-- key末尾 0 - 9
-- 普通にINSERT
INSERT INTO table_1 VALUES ('acctid_0000000000', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000001', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000002', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000003', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000004', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000005', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000006', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000007', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000008', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());
INSERT INTO table_1 VALUES ('acctid_0000000009', 'column_a_data', 'column_b_data', 'column_c_data', NOW(), NOW());


-- key末尾 10 - 99
-- SELECT INSERT (keyだけ変えながら、元データ複製INSERT)
INSERT INTO table_1 (select concat("acctid_000000001", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000002", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000003", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000004", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000005", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000006", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000007", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000008", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_000000009", substring(acct_id, 17)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000009' and acct_id >='acctid_0000000000');


複製を必要な分だけくりかえす。

qiita.rb

-- key末尾 100 - 999

INSERT INTO table_1 (select concat("acctid_00000001", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000002", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000003", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000004", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000005", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000006", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000007", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000008", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_00000009", substring(acct_id, 16)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000099' and acct_id >='acctid_0000000000');

-- key末尾 1000 - 9999

INSERT INTO table_1 (select concat("acctid_0000001", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000002", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000003", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000004", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000005", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000006", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000007", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000008", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');
INSERT INTO table_1 (select concat("acctid_0000009", substring(acct_id, 15)), column_a, column_b, column_c, NOW(), NOW() from table_1 where acct_id <='acctid_0000000999' and acct_id >='acctid_0000000000');


0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?