Re: How to write such a query

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/>
<https://www.jonathanrstrong.com>

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong(at)gmail(dot)com

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

On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer <shammat(at)gmx(dot)net> 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
>
>
>
>
>
>