Re: How to write such a query

A pretty good read / intro to the concept of keys in the relational model:

https://www.red-gate.com/simple-talk/sql/learn-sql-server/primary-key-primer-for-sql-server/

- Jon

<https://www.linkedin.com/in/jonstrong/>
<https://www.jonathanrstrong.com>

*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstrong(at)gmail(dot)com

*Quora Top Writer <https://www.quora.com/profile/Jonathan-R-Strong>*

On Fri, Sep 18, 2020 at 3:08 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 9/18/20 11:49 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
> > <adrian(dot)klaver(at)aklaver(dot)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(at)gmail(dot)com <mailto:jonathanrstrong(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>