MySQL inserting values from query

Question

So I have three tables. 1: Business - consists of a id and name, 2: Money - consists of person id and money they make, 3: Person - consists of person id and the business id that they work at.

I'm trying to go through all of the rows in business and insert into a new table the total money the business owes. I can easily get the sum of what a business owes total by doing a join on the money table and business table.

What I want to do is loop through all of the businesses and get the id and then use that id in my sql query to get the total money per business.

So I have something like "insert into business_payout (business_id,money_owed,date) values (Need to get all business id's here,I have the appropriate query already built but I don't know how to update it with the current business id,CURDATE())"

I'm pretty new to sql but what I've seen online is that a cursor may be appropriate. I was just wondering if this is the most efficient way to do this or is there something I'm missing.

Thanks for any help!

EDIT: UPDATED WITH TABLE STRUCTURE

Business: business_id int, business varChar

Money: person_id int, money float

Person: person_id int,business_id int

Sorry it isn't more detail/better format I tried to copy and paste from sql but it formatted it horribly on stack overflow.

Mysql <> Sql server. Please TAG the one you are using — Pரதீப்
– Pரதீப், Commented Dec 31, 2015 at 15:04
cursor is not appropriate when this can all be done in one insert query. please post your table structures for money, business, person, final_target sample data and expected output — amdixon
– amdixon, Commented Dec 31, 2015 at 15:04
Must have accidentally clicked sql server. Updated with table structure. — Zojirushi
– Zojirushi, Commented Dec 31, 2015 at 15:20

Felypp Oliveira · Accepted Answer · 2015-12-31 15:50:27Z

1

I think you need an INSERT/SELECT statement, like this:

insert into business_payout (business_id, money_owed, date) 
select bs.business_id, sum(mn.money), curdate()
from business bs
inner join person pr on 1=1
  and pr.business_id = bs.business_id
inner join money mn on 1=1
  and mn.person_id = pr.person_id
group by bs.business_id

I did not test this query, but I think it works properly.

answered Dec 31, 2015 at 15:50

Felypp Oliveira

2,1971 gold badge17 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Oooogi · Accepted Answer · 2015-12-31 15:40:53Z

1

First I would create a user-defined function that gets the business_id and outputs the total sum the business owes.

CREATE FUNCTION `GetBusinessTotalSum`(business_id int(11)) RETURNS decimal(10,3) CHARSET utf8
DETERMINISTIC
BEGIN
declare total_sum decimal(10,3);

...
/* set total_sum to the total sum */
...

RETURN total_sum;
END

Then I would do a simple insert into the new table. For the example lets name it "business_total_sums" with two fields:

business_id int

total_sum decimal(10,3)

To insert all total sums:

insert into business_total_sums (business_id, total_sum)
select business_id, GetBusinessTotalSum(business_id) from Business

And another thing, I would change the "Money" field in your tables from float to decimal because floating points don't keep the exact values you're putting in them, but a representation of nom/denom that produces (almost) the same value. e.g. 14.4999999999 is almost like 14.5. Decimal represent money fields better.

answered Dec 31, 2015 at 15:40

Oooogi

3834 silver badges14 bronze badges

1 Comment

Zojirushi Over a year ago

Marked other answer as correct as it was more straightforward. Thank you for the help though and the explanation (especially with decimal vs float)!!

Collectives™ on Stack Overflow

MySQL inserting values from query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related