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