Update the record from query result

Question

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 = "abc";

or I can have a query like this:

SELECT X.field1, Y.field2 FROM X, Y WHERE X.pk = Y.pk AND X.field3 = "abc";

Let's say when I skip to record 5, X.field1 will show "abc". I want this field to be updated to "def". So I type "def" 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.

Answer 1

I actually want to emulate what Access is doing - update the record by its position in a recordset

Access, like most RDBMS systems uses key values to identify and update rows. See:

To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Access workspace, or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.

Recordset.Update method (DAO)

Answer 2

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.