[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');