Solved

Help with mysql query

Posted on 2015-01-26
2
308 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

737 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