Re: How to write such a query - Mailing list pgsql-general
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.
On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer <shammat@gmx.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
pgsql-general by date: