Re: How to write such a query

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