Source language: Translate to:

SQL gurus...

Questions and information about creating Internet aware NeoBook applications. Including PHP, HTML, FTP, HTTP, Email, etc.

Moderator: Neosoft Support

SQL gurus...

Postby David de Argentina » Thu Jul 06, 2017 3:19 pm

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
User avatar
David de Argentina
 
Posts: 1559
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: SQL gurus...

Postby Rasl » Fri Jul 07, 2017 1:59 pm

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
Rasl
 
Posts: 129
Joined: Sat Apr 02, 2005 8:25 am
Location: Buenos Aires, Argentina

Re: SQL gurus...

Postby David de Argentina » Sat Jul 08, 2017 10:48 am

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
User avatar
David de Argentina
 
Posts: 1559
Joined: Mon Apr 04, 2005 4:13 pm
Location: Buenos Aires, Argentina

Re: SQL gurus...

Postby Rasl » Sun Jul 09, 2017 3:54 pm

Ok, this code is for MSSQL
Rasl
 
Posts: 129
Joined: Sat Apr 02, 2005 8:25 am
Location: Buenos Aires, Argentina


Return to NeoBook and the Internet

Who is online

Users browsing this forum: No registered users and 0 guests