[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Adding multiple rows to a table2 by fetching records from  table1

Posted on 2011-10-15
12
Medium Priority
?
476 Views
Last Modified: 2012-05-12
Greetings,

I am trying to derive a query which can do the following.

INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value) values ('','2','3','2011-10-15 04:12:06','3','Rent','0','');

id_client and actual_value(equivalent field on table 1 is account_state) has to be fetched from table1

SELECT id_client,account_state FROM `db1`.`table1` where id_r='68';((There are for eg n records ,so the first query should  insert n rows to table 2.

Secondly,from the account_state of each id_client,the value which shows in money column( in this case 3) should be deducted i.e from table1.

Thirdly,in table3,there is id_r and clientsLimit field.So the clientslimit has to be updated to clientsLimit=currentclientslimitvalue-(n*money)

where n is the total number of records in table 1 with id_r=68 and money is in this case 3.
Basically the money (which is 3$) is getting deducted from each client.

0
Comment
Question by:Sam2009
  • 6
  • 6
12 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36973145
simply by using SELECT instead of VALUES:

INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value) 
select id_client,'2','3','2011-10-15 04:12:06','3','Rent','0','' FROM FROM `db1`.`table1` where id_r='68';

Open in new window

0
 

Author Comment

by:Sam2009
ID: 36973194
client_type,money,data,type,description,invoice_id are not in table1 ,their values are all fixed.
i.e '2', '3','2011-10-15 04:12:06','3','Rent','0'
all records will have same value.id_client and actual_value has to be fetched from table1
table1 has only id_client and actual_value
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36973211
>client_type,money,data,type,description,invoice_id are not in table1 ,their values are all fixed.
does not matter, you can SELECT the constant values as shown.

fixing a syntax error from above:
INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value) 
select id_client,'2','3','2011-10-15 04:12:06','3','Rent','0', actual_value 
 FROM `db1`.`table1` where id_r='68';

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 

Author Comment

by:Sam2009
ID: 36973221
Ok let me try this and update asap.Meanwhile in table1 ,the money that is 3 has to be subtracted from each id_client's actualvalue .
0
 

Author Comment

by:Sam2009
ID: 36973287
Greetings,

The above query worked.actual_value is account_state in table 1.Now need to update teh account_state in table 1 by subtracting 3 form the account_state.

Regards
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36973398
you mean
INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value) 
select id_client,'2','3','2011-10-15 04:12:06','3','Rent','0', account_state - 3
 FROM `db1`.`table1` where id_r='68';

Open in new window

0
 

Author Comment

by:Sam2009
ID: 36973446
No,i didnt mean that

INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value)
select id_client,'2','3','2011-10-15 04:12:06','3','Rent','0', account_state
 FROM `db1`.`table1` where id_r='68';

This Query is perfect.
After this query is executed,we need to execute another query which will point table1
I think the query should look something like this

Update `db1`.`table1`set account_state=account_state-3 where id_r='68'

My requirement is that ,after the first query is executed,then the account_state for each id_client in table1 should by reduced by 3.Hope you got my point.so all the records in table1 where id_reseler='68' will have 3 deducted from the account_state .
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36973668
Well, I don't see the issue?
I mean, the syntax seems ok, what is the problem?
0
 

Author Comment

by:Sam2009
ID: 36973682
Hello,

I got all queries now.Now i need to know if we can link together and execute them in order

1)INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value)
select id_client,'2','3','2011-10-15 08:31:06','3','Rent','0', account_state
FROM `db1`.`table1` where id_reseller='68';

2)Update `db1`.`table1`set account_state=account_state-3 where id_reseller='68';

3)Update `db1`.`table3`set clientsLimit=clientsLimit+27 where id='68';

Here 27 is derived as follows.

As there are nine resultsets in first query,and total value deducted is 9*3=27

Can we automate and link together,if not,then we are done

Regards
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36973696
ok, I see:
Update `db1`.`table3`set clientsLimit=clientsLimit+ (3 * ( select count(*) from `db1`.`table1` where id_reseller='68')) where id='68';

Open in new window

0
 

Author Comment

by:Sam2009
ID: 36973724
Perfect ,Things are getting right.Thank you so much

Now here is my queries

1)INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value)
select id_client,'2','3','2011-10-15 08:31:06','3','Rent','0', account_state
FROM `db1`.`table1` where id_reseller='68';

2)Update `db1`.`table1`set account_state=account_state-3 where id_reseller='68';

3)Update `db1`.`table3`set clientsLimit=clientsLimit+ (3 * ( select count(*) from `db1`.`table1` where id_reseller='68')) where id='68';

These queries have to be executed in order and have to be executed first of every month,so when we say it has to be executed next on '2011-11-01 00:00:00' .this is the data field.Now my last requirement,Is there any way we can automate the three queries to be executed in order and first of everymonth automatically

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36973796
ok, "no problem":
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html
you will find the function CURDATE():
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_curdate

so, if you run the "script" on first day of every month, you will get that nice value you want in there:
INSERT INTO `db1`.`table2` (id_client,client_type,money,data,type,description,invoice_id,actual_value)
select id_client,'2','3',curdate(),'3','Rent','0', account_state
FROM `db1`.`table1` where id_reseller='68';

Open in new window


to run this on first of every month is an operating system scheduler question..
use the mysql command line to run the sql:
http://dev.mysql.com/doc/refman/5.5/en/mysql.html

this should clarify the remanining needs.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question