• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Help with mysql query

Hi,

Let me first describe about table and table data


table 1 have 2 columns for example

id, count
1, 1
1, 1
1, 1
1, 2
1, 5
1, 2
1, 1
1, 1
1, 1
1, 1

another table has two colomuns as

id,limit
1,200

now what I want to do is by the end of the day take sum of count from table A, and deduct it from table 2 limit, and than move all records into different tables, I have them in bits and pieces but I don't know what would be a single query to do it.

First query : select sum(count) from tablea where id='1';

let's suppose it returns 16

second query would be : update tableb set limit=limit-16 where id='1';

and third query would be

delete from tablea where id=1


I want these three queries to be a single query.
0
fahadalam
Asked:
fahadalam
1 Solution
 
LowfatspreadCommented:
you can't do itwith a single sql statement...

you can do it in to statements

update table2 set limit=limit-(select count(*) from table1 where table1.id=table2.id)
delete from table1.....

you could put both statements within a stored procedure...

or you could have a delete trigger on table 1 which when the rows where deleted adjusted the data on table2 ...

which versionof mysql are you using?
0
 
guveraCommented:
You can create the stored proc and keep this query that you have mentioned. Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now