Re: How to write such a query - Mailing list pgsql-general
Ken,
On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:
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;
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 SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
pgsql-general by date: