Solved

Help with mysql query

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

627 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