Looping in SQL

Classic Servoy
1

Hi guys,
i have been googling about this but doesn’t seem to find anything that can help.
I am looking for a SQL statement that can do an update of a column using SELECT.
What I am writing is nonsens, but it show what I want:

UPDATE client
SET adres_id = 
          (SELECT adres_id 
          FROM adres 
          WHERE client_id = XXX 
          AND blah=1)
WHERE client_id = XXX

I am using a loop-construction in Servoy, but it a slow process, especially if you have a lot of client, and you do it 3 similar actions per client. This is a one time action after converting a new customer to our software.

Thanx in advance

2

Time for a google training then! Thousands of examples can be found about updating columns using a subselect.

3

I realize this was just an example but for ‘looping’ through your records using SQL to perform an UPDATE for all records you might want to ‘link’ your tables and then you’ll just update all records in the database and avoid looping in Servoy all together. (With the ‘client_id = XXX’ it appears you are gathering the id on a record by record basis). So in your example you might consider instead linking the ‘client’ and ‘adres’ tables as to update the entire client table:

UPDATE client c
SET c.adres_id = a.adres_id
FROM client c JOIN adres a ON c.client_id = a.client_id
WHERE a.blah = 1

This is presuming from your post that there is an adres_id column in your client table. This then can be run directly without Servoy directly on your backend or as a RAWSQL update within Servoy and no looping. Maybe that was what you meant but it wasn’t clear from your post so thought I’d pass that on.