Solved

Help with mysql query

Posted on 2015-01-26
2
295 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
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
Comment Utility
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
Comment Utility
You can create the stored proc and keep this query that you have mentioned. Thanks.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
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…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now