Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with mysql query

Posted on 2015-01-26
2
Medium Priority
?
316 Views
Last Modified: 2015-02-05
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
Comment
Question by:fahadalam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 1500 total points
ID: 40570275
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
 
LVL 8

Expert Comment

by:guvera
ID: 40571302
You can create the stored proc and keep this query that you have mentioned. Thanks.
0

Featured Post

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 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