mysql transfer query

Go To


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.

2012-04-04 17:56
by LordZardeck


This should do what you want.

INSERT INTO `Charge Table` SELECT NULL, id, charges, 1, NOW(), 'Initial balance' FROM `User Table`
2012-04-04 17:58
by Glen Solsberry
So if I understand correctly, this should work?

INSERT INTO bills (id, patronid, amount, itemid, description, date_charged, type) VALUES( SELECT NULL, id, fines, null, 'Initial balance', NOW(), 1 FROM patrons - LordZardeck 2012-04-04 18:08

actually, that didn't. I tried it and I got an error near 'SELECT NULL, id, fines, null - LordZardeck 2012-04-04 18:15
No, you don't need the 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
Ah, ok. thanks - LordZardeck 2012-04-04 18:16