I updated the way my program works with fines and charges and now I need to transfer/migrate the old data to the new format. The way it used to work was fines/charges were stored in a field on the users's record. Now there is an entirely new table used to track each charge. I'd like to simply take that charge field and and 1 record to the billing table with that charge.
User table: id as INT, name as VARCHAR, charges as DECIMAL(10, 2)
Charge Table: id as INT, user_id as INT, amount as DOUBLE, type as INT, date_applied as DATETIME, description as VARCHAR
Lets say I have a user record: (id => 1, name => 'john doe', charges => 4.20) and want to transfer that to a charge record of
(id => 1, user_id => 1, amount => 4.20, type => 1, date_applied => (whatever time the transfer is taking place), description => 'Initial balance')
How can I do this one time transfer in a SQL query? I'd rather not write up a PHP script for this one time deal.
This should do what you want.
INSERT INTO `Charge Table` SELECT NULL, id, charges, 1, NOW(), 'Initial balance' FROM `User Table`
VALUES or the parens. You could do just INSERT INTO bills (id, patron_id, amount, item_id, description, date_charged, type) SELECT NULL, id, fines, null, 'Initial balance', NOW(), 1 FROM patronsGlen Solsberry 2012-04-04 18:15
INSERT INTO
bills(id, patronid, amount, itemid, description, date_charged, type) VALUES( SELECT NULL, id, fines, null, 'Initial balance', NOW(), 1 FROMpatrons- LordZardeck 2012-04-04 18:08