Insert multiple row with condition checking on other table.. only single query

Question

Table1: usermaster

uid uname
1    abc
2    xyz
3    pqr
4    def

Table2: transactionmaster

tid uid amount type
1    1  100    1
2    2  500    1
2    2  500    2
3    1  350    1
3    1  150    2

type in transaction table:

1 for capital
2 for interest(5% of total capital)

Now, I want to calculate the interest of capital amount and ad interest of capital values 5% every month. Query should be passed which is : add interest entry in transactionmaster table automatically for both users who have capital.

Result should like this in transactionmaster table.

tid uid amount type
1    1  100    1
2    2  500    1
3    1  600    1
4    1  35     2
5    2  25     2

Here interest also count automatically 5%.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 09 '17 at 12:30

Thomas G · Answer 1 · 2017-02-09T13:02:11.727

Something like this should do the trick :

INSERT INTO transactionmaster (uid, amount, type)
SELECT uid, ((SUM(amount) / 100) * 5), 2
FROM transactionmaster
WHERE type = 1 
GROUP BY uid

I assumed that the tid field is an autoicrement

Edit: The query above is a oneshot. ie it will systematically create "type 2" entries for all uid that have "type 1". In other words, if you use it several times you'll end up with duplicates "type 2" entries.

If you want to INSERT "type 2" rows for only "type 1" that don't have "type 2" rows yet, you can do this :

INSERT INTO transactionmaster (uid, amount, type)
SELECT t1.uid, ((SUM(t1.amount) / 100) * 5), 2
FROM transactionmaster t1 
LEFT JOIN transactionmaster t2 ON t1.uid=t2.uid AND t1.type=1 AND t2.type=2 
WHERE t2.tid IS NULL
GROUP BY t1.uid

Edit 2 to answer your comment.

Suppose you create an intrustmaster table having this structure :

loweramt | higheramt | perc
---------------------------
100      | 199       | 5
200      | 399       | 4

The oneshot query will become this :

INSERT INTO transactionmaster (uid, amount, type)
SELECT T.uid, ((totamt /100) * i.perc), 2
FROM 
(
    SELECT uid, (SUM(amount) / 100) as totamt
    FROM transactionmaster
    WHERE type = 1 
    GROUP BY uid
) T
INNER JOIN intrustmaster I
  ON t.totamt BETWEEN i.loweramt AND i.higheramt

let me suggest one thing here if intrust is different for every stage then how it possible, suppose i make one table intrustmaster and there is 3 slab, 100-200 intrust is 5%, 200-400 intrust is 4% and 500-1000 5% then what changes needed? — Vinay Vadachhak, Feb 09 '17 at 12:46
yes, total amount for uid, intrustmaster fields (id,min,max,intrust_rate). data(1,100,200,5)(2,201,400,4)(3,401,500,5) — Vinay Vadachhak, Feb 09 '17 at 12:58
answered but chitchatting like this is not a proper way to Q&A on SO. So if you have further questions, edit your answer and add details, or create a new question with more specific elements. — Thomas G, Feb 09 '17 at 13:05

score 0 · Answer 2 · answered Feb 09 '17 at 12:36

To get the result automatically on every month you need to schedule your SQL query using MySQL events.

Here is the reference 1) http://www.infotuts.com/schedule-sql-query-using-phpmyadmin-mysql-events/

Get the Sum of capital from transactionmaster

select sum(amount) from transactionmaster where uid = 13 and type=1

Now get count the interest

select sum(amount) * (5 / 100)  as interest from transactionmaster where uid=13 and type=1

simple!

Insert multiple row with condition checking on other table.. only single query

2 Answers2