jsp - mysql data substraction from column of one table to column of another table -

i have 2 different table named 1. users 2. transaction , both table have 'amount' name column.

i need subtract users-> amount column transaction-> amount column , store subtracted value users-> amount column.

table structure:

users have column: id(primary key),username,amount transaction have column: id (primary key),username,transaction_id,amount 

here want users.amount = users.amount - transaction.amount id of both table same.

try following update query:

update users t1 inner join transaction t2     on t1.id = t2.id set t1.amount = t1.amount - t2.amount 

this assumes that, said, id columns both tables identifies user. assumes want update every record in users table without restriction. if not, can add where clause update query.