alex davies alex davies - 2 years ago 71
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

table with a column
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
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
table from the
query and use it in the
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 Source

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

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


  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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download