Hi overthere,
I'm trying to build a SQL expression in order to generate all clients->balance at the same time.
I use 2 tables:
a) Table clientes:
id as autoincrement
balance as numeric, 12.2
b) operations:
client_id as numeric, 10
debits as numeric, 12.2
credits as numeric, 12.2
Another fields are not relevant.
I'm doing some like this:
[syntax=sql]UPDATE Clients set Clients.balance = t.suma
from Clients inner join (
select operations.client_id, sum(operations.debits) suma
from operations
group by operations.client_id ) t
on Clients.id = operations.client_id)[/syntax]
Obviously, the expression must substract the credits values but nothing work yet.
Any ideas ?
Thanks in advance,
David de Argentina
SQL gurus...
Moderator: Neosoft Support
-
- Posts: 1596
- Joined: Mon Apr 04, 2005 4:13 pm
- Location: Buenos Aires, Argentina
- Contact:
Re: SQL gurus...
Try this:
UPDATE Clients set Clients.balance = t.suma
from Clients
inner join (
select client_id, sum(credits-debits) suma
from operations
group by client_id ) t
on Clients.id = t.client_id
salu2
UPDATE Clients set Clients.balance = t.suma
from Clients
inner join (
select client_id, sum(credits-debits) suma
from operations
group by client_id ) t
on Clients.id = t.client_id
salu2
-
- Posts: 1596
- Joined: Mon Apr 04, 2005 4:13 pm
- Location: Buenos Aires, Argentina
- Contact:
Re: SQL gurus...
Thanks Rasl,
Your sample produces mysql error 1064.
I had do the job using php.
Not much ellegant, but very practice.
Greetings from Buenos Aires,
David de Argentina
Your sample produces mysql error 1064.
I had do the job using php.
Not much ellegant, but very practice.
Greetings from Buenos Aires,
David de Argentina
Re: SQL gurus...
Ok, this code is for MSSQL