Adding multiple rows to a table2 by fetching records from table1
Posted on 2011-10-15
I am trying to derive a query which can do the following.
INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value) values ('','2','3','2011-10-15 04:12:06','3','Rent','0','');
id_client and actual_value(equivalent field on table 1 is account_state) has to be fetched from table1
SELECT id_client,account_state FROM `db1`.`table1` where id_r='68';((There are for eg n records ,so the first query should insert n rows to table 2.
Secondly,from the account_state of each id_client,the value which shows in money column( in this case 3) should be deducted i.e from table1.
Thirdly,in table3,there is id_r and clientsLimit field.So the clientslimit has to be updated to clientsLimit=currentclientslimitvalue-(n*money)
where n is the total number of records in table 1 with id_r=68 and money is in this case 3.
Basically the money (which is 3$) is getting deducted from each client.