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