Update the record from query result Lets say I have a table X with following definition:
CREATE TABLE X( id INT PRIMARY KEY, field1 varchar(100), field2 int, field3 double);

Then I issue a query:
SELECT field1, field2 from X;

Let's say this query returned 10 records.
Is there a way to write a query to update the record #5 from the SELECT? What would the query will look like? Or I will have to include id in that query, somehow find its value in a record #5 and then use it in a query below?
Something like UPDATE X SET field1 = 'def' WHERE....
I have a problem with the condition - what to write for the WHERE clause. I'm working with multiple DBMSes, so need a generic way if possible.
More details:
I can have a query like this:
SELECT field1, field2 FROM X WHERE field3 = &quot;abc&quot;;

or I can have a query like this:
SELECT X.field1, Y.field2 FROM X, Y WHERE X.pk = Y.pk AND X.field3 = &quot;abc&quot;;

Let's say when I skip to record 5, X.field1 will show &quot;abc&quot;. I want this field to be updated to &quot;def&quot;. So I type &quot;def&quot; and skip to record 6. In both cases I want to update record 5 X.field1 of the resulting recordset.
As you can see I'm going by the records in a recordset (results of executing a query). I want to emulate what Access is doing.
Let's say I open the new form in Access and I base it on the query.
I re-arrange the field on the form to my liking and execute the form.
Then I go to display the record #5 in the form and then update a field.
This update is instantaneous, no matter the complexity of the query behind the form.
SELECT  *
FROM    Table t
WHERE   1 = 1
        AND EXISTS (Define criteria here)

The above is a select statement. It returns rows stored in a table.
To make this an UPDATE statement, use exactly the same syntax, except instead of using SELECT syntax, use UPDATE syntax.
UPDATE  t
SET     Column1 = 'SomeNeatValue'
        ,Column2 = 'AnotherNeatValue'
FROM    Table t
WHERE   1 = 1
        AND EXISTS (Define criteria here)
    

How you identify which records to select/update is on the developer. If you need assistance, I'd start at www.w3schools.com and bone up on query syntax.