Re: How to write such a query

On 9/18/20 11:49 AM, Igor Korot wrote:
> Hi, Adrian,
>
> On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
> <adrian.klaver@aklaver.com> wrote:
>>
>> On 9/18/20 10:46 AM, Igor Korot wrote:
>>> Hi, Johnathan,
>>>
>>> On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
>>> <jonathanrstrong@gmail.com <mailto:jonathanrstrong@gmail.com>> wrote:
>>>
>>>      Are you looking to arbitrarily update the field in the fifth row, or
>>>      can the row that needs to be updated be isolated by some add'l
>>>      attribute? What's the use case?
>>>
>>>
>>> What do you mean?
>>> I don't have any other attributes.
>>>
>>> I want to understand how to emulate MS Access behavior, where you have a
>>> form
>>> with the arbitrary query, then you can go to any record in that form and
>>> update any field.
>>>
>>> Is it even possible from the "pure SQL" POV? Or Access is doing some
>>> VBA/DB/4GL magic?
>>>
>>
>> When you are updating a record in a form the framework(Access in your
>> case) is using some identifier from that record to UPDATE that
>> particular record in the database. From when I used Access, I seem to
>> remember it would not give you INSERT/UPDATE capability on a form unless
>> you had specified some unique key for the records. So you need to find
>> what the key(generally a PRIMARY KEY) is and use that to do the UPDATE.
>
> But now the question becomes
>
> How to find what the primary key (or UNIQUE identifier) value is
> for row 5 in the recordset?

You defined them:

CREATE TABLE X(id INTEGER PRIMARY KEY, field1 char(50), field2 int);
CREATE TABLE Y(id INTEGER PRIMARY KEY, field1 char, field2 double(10, 2));

How you fetch that value is going to depend on where the record set is
being presented and how the record to be UPDATEd is selected? If you are
using some sort of framework/form to display the records it will have
some mechanism to gather the information(data) on select and then you
can use the PRIMARY KEY value from that data to do the UPDATE. If you
want a more precise answer then you will need to provide a complete
example of what you are doing.

>
> Thank you.
>
>>
>>> Thank you.
>>>
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.klaver@aklaver.com


--
Adrian Klaver
adrian.klaver@aklaver.com