0

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.

0

2 Answers 2

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)

0
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.

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Jan 3 at 10:24

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.