SQL gurus...

[David de Argentina]

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

[Rasl]

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

[David de Argentina]

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

[Rasl]

Ok, this code is for MSSQL