alex davies alex davies - 1 month ago 13
MySQL Question

How to add a value to already existing value in column field and then insert the update value into another table

I Have a

members
table with a column
amount
which already has an integer value in it, I wish to add to that value and then use its contents to update another table called
accounts
which holds the amount being credited and the new balance (i.e After addition)

$this->query_array = array(':amt' => $amount, ':uid' => $userid, ':dat' => $date );

$this->query_string = "UPDATE members SET amount = amount + :amt;
INSERT INTO accounts VALUES ( :uid, :amt, members.amount + :amt, :dat);";


Thats my query above, am trying to get the
members.amount
table from the
update
query and use it in the
insert
query is there a better way, one that works than what I tried.

Sample Data :

Members Table userid amount
---------------------
u123 4000
y123 5000

Accounts Table userid credit balance date


Accounts table
is currently empty so what I wanna do is add 300 to all values in memebrs table and then insert into accounts something like this.

Accounts Table userid credit balance date
-------------------------------------
u123 300 4300 11-11-11
y123 300 5300 11-11-11

Answer

I would use an insert ... select ... statement to achieve the desired outputs:

INSERT INTO accounts
SELECT members.userid, :amt, members.amount,  :dat FROM members

Notes:

  1. I'm using the members.amount in place of members.amount + :amt because the :amt has already been added once to members.amount.

  2. Your update statement updates all records within the members table, yet the insert statement would create records for a specific user only. I believe you should restrict the update statement to the specific user only or not restrict the insert to a specific user. I chose the latter approach.

  3. If you want to store the balance in a denormalised way, then I would rather use a trigger on the accounts table to update the balance in the members table, than to use 2 statements.