Page 1 of 1

SQL gurus...

PostPosted: Thu Jul 06, 2017 3:19 pm
by 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: [ Download ] [ Hide ]
Using SQL Syntax Highlighting
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)
Parsed in 0.006 seconds, using GeSHi 1.0.8.10


Obviously, the expression must substract the credits values but nothing work yet.

Any ideas ?

Thanks in advance,
David de Argentina

Re: SQL gurus...

PostPosted: Fri Jul 07, 2017 1:59 pm
by 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

Re: SQL gurus...

PostPosted: Sat Jul 08, 2017 10:48 am
by 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

Re: SQL gurus...

PostPosted: Sun Jul 09, 2017 3:54 pm
by Rasl
Ok, this code is for MSSQL