Re: How to write such a query

On Fri, Sep 18, 2020 at 1:26 PM Ron <ronljohnsonjr@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;

Cheers,

Ken

 

--

AGENCY Software  

A Free Software data system

By and for non-profits

http://agency-software.org/

https://demo.agency-software.org/client

ken.tanzer@agency-software.org

(253) 245-3801

Subscribe to the mailing list to

learn more about AGENCY or

follow the discussion.