Re: How to write such a query - Mailing list pgsql-general
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 queryI 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;
tmp.row_number=5 AND x.field1=tmp.field1;
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-general by date: