How to write such a query

Are you looking to arbitrarily update the field in the fifth row, or can
the row that needs to be updated be isolated by some add'l attribute?
What's the use case?

- Jon

<https://www.linkedin.com/in/jonstrong/&gt;
<https://www.jonathanrstrong.com&gt;

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong@gmail.com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong&gt;*

On Fri, Sep 18, 2020 at 1:27 PM Igor Korot <ikorot01@gmail.com> wrote:

Hi Igor,

On 18. Sep, 2020, at 19:29, Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Consider following

[code]
CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.

How do I write a WHERE clause in the

[code]
UPDATE X.field1 SET X.field1 = '<some_string>' WHERE....
[/code]

Thank you.

update x set field1='<some string>' where id=5;

Cheers,
Paul

Hi, Paul

On Fri, Sep 18, 2020 at 12:34 PM Paul Förster <paul.foerster@gmail.com> wrote:

Hi Igor,

On 18. Sep, 2020, at 19:29, Igor Korot <ikorot01@gmail.com> wrote:

Hi,
Consider following

[code]
CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.

How do I write a WHERE clause in the

[code]
UPDATE X.field1 SET X.field1 = '<some_string>' WHERE....
[/code]

Thank you.

update x set field1='<some string>' where id=5;

How do you know that the row #5 will have an X.id field 5?

Thank you.

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong <jonathanrstrong@gmail.com>
wrote:

Are you looking to arbitrarily update the field in the fifth row, or can
the row that needs to be updated be isolated by some add'l attribute?
What's the use case?

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

Thank you.

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
<jonathanrstrong@gmail.com <mailto:jonathanrstrong@gmail.com>> wrote:

Are you looking to arbitrarily update the field in the fifth row, or
can the row that needs to be updated be isolated by some add'l
attribute? What's the use case?

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

When you are updating a record in a form the framework(Access in your
case) is using some identifier from that record to UPDATE that
particular record in the database. From when I used Access, I seem to
remember it would not give you INSERT/UPDATE capability on a form unless
you had specified some unique key for the records. So you need to find
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

@Adrian -

Using a unique key value or otherwise isolating a specific record via
selection against values in its attributes is certainly preferable to
choosing a row to update via its position in a result set, unless the use
case actually makes use of that position info as a meaningful descriptor of
the data in some fashion.

- Jon

<https://www.linkedin.com/in/jonstrong/&gt;
<https://www.jonathanrstrong.com&gt;

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong@gmail.com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong&gt;*

On Fri, Sep 18, 2020 at 1:58 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Igor Korot schrieb am 18.09.2020 um 19:29:

[code]
CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.

There is no such thing as "row 5" in a relational database.

Rows in a table have no inherent sort order. The only way you can identify
a row, is by the value of its primary (or unique) key. Not by "position".

The only way you can identify "row 5" is, if you use an ORDER BY to
define a sort order on the result - but that position is only valid
for that _result_, it has no meaning for the actual table data.

Which brings us back to the fact, that the only way to (uniquely) identify
a row in a table is to specify its primary key value in the WHERE clause

On 9/18/20 11:13 AM, Jonathan Strong wrote:

@Adrian -

Using a unique key value or otherwise isolating a specific record via
selection against values in its attributes is certainly preferable to
choosing a row to update via its position in a result set, unless the
use case actually makes use of that position info as a meaningful
descriptor of the data in some fashion.

The bigger issue is deciding what attribute of the selected row is be
used to do the UPDATE. Unless it is the PRIMARY KEY(or other UNIQUE
key(s)) then you very likely are going to UPDATE more then you bargained
for.

- Jon

<https://www.linkedin.com/in/jonstrong/&gt; <https://www.jonathanrstrong.com&gt;

*Jonathan Strong*

--
Adrian Klaver
adrian.klaver@aklaver.com

Yes...absolutely. Short of using ORDER BY, the order of a multi-row result
set can be arbitrary, with "row position" having no significant meaning.

This gets back to understanding set theory, the relational model, the
various types of keys (primary, candidate, foreign, etc.). Truly crucial to
understand the model in order to write correctly functioning and reliable
code.

- Jon

<https://www.linkedin.com/in/jonstrong/&gt;
<https://www.jonathanrstrong.com&gt;

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong@gmail.com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong&gt;*

On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer <shammat@gmx.net> wrote:

Yes - 100%

- Jon

<https://www.linkedin.com/in/jonstrong/&gt;
<https://www.jonathanrstrong.com&gt;

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong@gmail.com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong&gt;*

On Fri, Sep 18, 2020 at 2:22 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
<jonathanrstrong@gmail.com <mailto:jonathanrstrong@gmail.com>> wrote:

Are you looking to arbitrarily update the field in the fifth row, or
can the row that needs to be updated be isolated by some add'l
attribute? What's the use case?

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

When you are updating a record in a form the framework(Access in your
case) is using some identifier from that record to UPDATE that
particular record in the database. From when I used Access, I seem to
remember it would not give you INSERT/UPDATE capability on a form unless
you had specified some unique key for the records. So you need to find
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

Thank you.

On 9/18/20 11:49 AM, Igor Korot wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
<jonathanrstrong@gmail.com <mailto:jonathanrstrong@gmail.com>> wrote:

Are you looking to arbitrarily update the field in the fifth row, or
can the row that needs to be updated be isolated by some add'l
attribute? What's the use case?

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

When you are updating a record in a form the framework(Access in your
case) is using some identifier from that record to UPDATE that
particular record in the database. From when I used Access, I seem to
remember it would not give you INSERT/UPDATE capability on a form unless
you had specified some unique key for the records. So you need to find
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

You defined them:

CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));

How you fetch that value is going to depend on where the record set is
being presented and how the record to be UPDATEd is selected? If you are
using some sort of framework/form to display the records it will have
some mechanism to gather the information(data) on select and then you
can use the PRIMARY KEY value from that data to do the UPDATE. If you
want a more precise answer then you will need to provide a complete
example of what you are doing.

Thank you.

Thank you.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

A pretty good read / intro to the concept of keys in the relational model:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/primary-key-primer-for-sql-server/

- Jon

<https://www.linkedin.com/in/jonstrong/&gt;
<https://www.jonathanrstrong.com&gt;

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong@gmail.com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong&gt;*

On Fri, Sep 18, 2020 at 3:08 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 9/18/20 1:49 PM, Igor Korot wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
<adrian.klaver@aklaver.com> wrote:

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
<jonathanrstrong@gmail.com <mailto:jonathanrstrong@gmail.com>> wrote:

Are you looking to arbitrarily update the field in the fifth row, or
can the row that needs to be updated be isolated by some add'l
attribute? What's the use case?

What do you mean?
I don't have any other attributes.

I want to understand how to emulate MS Access behavior, where you have a
form
with the arbitrary query, then you can go to any record in that form and
update any field.

Is it even possible from the "pure SQL" POV? Or Access is doing some
VBA/DB/4GL magic?

When you are updating a record in a form the framework(Access in your
case) is using some identifier from that record to UPDATE that
particular record in the database. From when I used Access, I seem to
remember it would not give you INSERT/UPDATE capability on a form unless
you had specified some unique key for the records. So you need to find
what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.

But now the question becomes

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row 5". To
update the 5th record that you've fetched, you increment a counter each time
you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg'
WHERE id = <thekeyvalue>;

--
Angular momentum makes the world go 'round.

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row 5". To
update the 5th record that you've fetched, you increment a counter each
time
you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg'
WHERE id = <thekeyvalue>;

It seems worth mentioning for benefit of the OPs question that there _is_ a
way to get a row number within a result set. Understanding and making good
use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()* from X, Y WHERE X.id =
Y.id -- ORDER BY ____?

That row number is going to depend on the order of the query, so it might
or might not have any meaning. But if you queried with a primary key and a
row number, you could then tie the two together and make an update based on
that.

Cheers,
Ken
--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/&gt;*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client&gt;*
ken.tanzer@agency-software.org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request@lists.sourceforge.net?body=subscribe> to
learn more about AGENCY or
follow the discussion.

On Sep 18, 2020, at 1:45 PM, Ken Tanzer <ken.tanzer@gmail.com> wrote:

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row 5". To
update the 5th record that you've fetched, you increment a counter each time
you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg'
WHERE id = <thekeyvalue>;

It seems worth mentioning for benefit of the OPs question that there _is_ a way to get a row number within a result set. Understanding and making good use of that is an additional matter.

SELECT X.field1, Y.field2,row_number() OVER () from X, Y WHERE X.id = Y.id -- ORDER BY ____?

That row number is going to depend on the order of the query, so it might or might not have any meaning. But if you queried with a primary key and a row number, you could then tie the two together and make an update based on that.

If “row 5” as seen by the OP has no distinguishing characteristic directing OP to edit that tuple then he’s in a world of hurt, well beyond the reach of anyone here.

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:

How to find what the primary key (or UNIQUE identifier) value is

for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row 5". To
update the 5th record that you've fetched, you increment a counter each
time
you fetch a row, and when you read #5, do an UPDATE X SET field1 =
'blarg'
WHERE id = <thekeyvalue>;

It seems worth mentioning for benefit of the OPs question that there _is_
a way to get a row number within a result set. Understanding and making
good use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()* from X, Y WHERE X.id =
Y.id -- ORDER BY ____?

That row number is going to depend on the order of the query, so it might
or might not have any meaning. But if you queried with a primary key and a
row number, you could then tie the two together and make an update based on
that.

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.

Thank you.

Igor Korot schrieb am 18.09.2020 um 22:18:

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 assume that they query the database to find the primary key for the table in question.
Once the primary key columns are known (and part of the result), they generate the appropriate
UPDATE statement with a WHERE clause based on the the values in the result to update
the row that was changed.

Thomas

On 9/18/20 3:18 PM, Igor Korot wrote:

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer <ken.tanzer@gmail.com
<mailto:ken.tanzer@gmail.com>> wrote:

How to find what the primary key (or UNIQUE identifier) value is
for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row
5". To
update the 5th record that you've fetched, you increment a counter
each time
you fetch a row, and when you read #5, do an UPDATE X SET field1 =
'blarg'
WHERE id = <thekeyvalue>;

It seems worth mentioning for benefit of the OPs question that there
_is_ a way to get a row number within a result set.  Understanding and
making good use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()* from X, Y WHERE X.id
= Y.id -- ORDER BY ____?

That row number is going to depend on the order of the query, so it
might or might not have any meaning. But if you queried with a primary
key and a row number, you could then tie the two together and make an
update based on that.

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.

--
Angular momentum makes the world go 'round.