Ken, On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer <ken.tan...@gmail.com> wrote:
> On Fri, Sep 18, 2020 at 1:26 PM Ron <ronljohnso...@gmail.com> wrote: > >> On 9/18/20 3:18 PM, Igor Korot wrote: >> > Thank you for the info. >> My problem is that I want to emulate Access behavior. >> >> As I said - Access does it without changing the query internally (I >> presume). >> >> I want to do the same with PostgreSQL. >> >> I'm just trying to understand how to make it work for any query >> >> I can have 3,4,5 tables, query them and then update the Nth record in the >> resulting recordset. >> >> Access does it, PowerBuilder does it. >> >> I just want to understand how. >> >> >> They do it by hiding the details from you. >> >> > That's true. And Igor--people are asking you some good questions about > why and design and such that you'd probably be well-advised to think about > and respond to. > > So I'm not saying you should do this, but responding to your question > specifically, and what the "details" are that Ron alludes to, one way to > get the result you're asking about is to run your query adding on row > numbers (pay attention to your ordering!), and then reference that result > set from an update to get the primary key you want. So I didn't test it, > but something roughly like this: > > WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y > WHERE X.id = Y.id ) UPDATE x SET ... FROM tmp WHERE > tmp.row_number=5 AND x.field1=tmp.field1; > I didn't know that row_number() function exists and it is available across different DBMSes. I will test that query later. Thank you. Now one other little thing: could you point me to the documentation that explains the meaning of the "window function"? > Cheers, > Ken > > > > -- > AGENCY Software > A Free Software data system > By and for non-profits > *http://agency-software.org/ <http://agency-software.org/>* > *https://demo.agency-software.org/client > <https://demo.agency-software.org/client>* > ken.tan...@agency-software.org > (253) 245-3801 > > Subscribe to the mailing list > <agency-general-requ...@lists.sourceforge.net?body=subscribe> to > learn more about AGENCY or > follow the discussion. >
