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.

  • 3
    There is no such thing as "row #5" - rows in a table have no implied sort order. – a_horse_with_no_name Sep 3 '20 at 16:46
  • how do you know which row is #5? what if I switch the order by clause ... still want row #5 or what used to be row #5? some RDBMSs have windows functions (eg, SQLServer/rownumber(),) other RDBMSs (eg, Sybase ASE) do not support windows functions ... perhaps some combination of top/max/min, but at some point you need to determine how you define #5 ... which in turn will (usually) come down to some variation of a where clause – markp-fuso Sep 3 '20 at 17:26
  • @markp-fuso, if you switch the order by it will be different recordset results, right? What I wrote is just an example. Basically my question is - how to determine what to update from a resulting recordset? I can have 2 tables connected through the primary key and query both of them but want only row X in the result to be updated. – Igor Sep 3 '20 at 17:45
  • @a_horse_with_no_name, we are not talking about a table - we are talking about the result of the SELECT query – Igor Sep 3 '20 at 17:46
  • The Access behavior you're describing is provided by the client portion of Access ... you sort/adjust the displayed rows and then update row #5; under the covers that display set has a link back to the original row in the database, so when you say to update row #5 in the display, the Access client effectively joins the display row back to the database via the under-the-covers key; trying to emulate this, in generic SQL, for a wide range of RDBMSs isn/t doable; you need to a) find (or write) a client-side app that can do what you want or b) use custom SQL for each different RDBMS – markp-fuso Oct 11 '20 at 17:54
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.

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.

  • well I understand all this. However my criteria is not connected to the table or the primary key. They are directly connected to the rowset. If I can identify in your SELECT statement row number 5 (not the PK with the value 5), I will use it inside the UPDATE statement. But I just don't know how. Is there a way? – Igor Sep 8 '20 at 23:24
  • All DBMS clients has a way to identify which row to update based on the "cursor position". For example, in ACCESS if I have a form based on a query, which is execued and I go to the record 5 and change column, ACCESS will update the appropriate row/column. It will update the column at row 5 in a recordset, not row 6 or row 4. Because if after I query the table with the data I will see the updated data exactly where it should be. I want to do something similar, but cross-DBMS. – Igor Sep 8 '20 at 23:28
  • You say you understand all this, but it doesn't seem that way. What you are asking is RBAR (row by agonizing row) operation; or a loop, and is outside of the scope of set-based operations. I suspect you may be going about solving your problem in a way that is harder than necessary. Can you provide some better data, and what real world problem are you trying to solve? – Mike Petri Sep 9 '20 at 18:31
  • I actually want to emulate what Access is doing - update the record by its position in a recordset, rather than position based on the PK/some other criteria. I mean Access does it - it updates the record N if the user changes the field when the form/query displays record N. So, why I can't do the same? And it does it independently of the query complexity. – Igor Sep 10 '20 at 0:05
  • I also tried to update the OP. – Igor Sep 10 '20 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.