Re: How to write such a query?

[Dmitry Igrishin]

On Thu, Jan 6, 2022, 09:40 Igor Korot  wrote:

> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>
Named parameters of prepared statements are implemented in my C++ library
Pgfe.

Re: How to write such a query?

[Igor Korot]

Hi, Armul,

On Thu, Jan 6, 2022 at 12:46 AM Amul Sul  wrote:
>
> See prepare statement : 
> https://www.postgresql.org/docs/current/sql-prepare.html

The documentation is talking about a way to do it like:

SELECT a, b, c FROM foo WHERE id = $1,

which is equivalent to the

SELECT a, b, c FROM foo WHERE id = ?;

i.e. using unnamed parameter.

Thank you.

>
> On Thu, Jan 6, 2022 at 12:10 PM Igor Korot  wrote:
> >
> > Hi, ALL,
> > In SQLite you can write:
> >
> > SELECT a, b, c FROM foo WHERE id = :id;
> >
> > where ":id" is the named parameter.
> >
> > The query above is similar to
> >
> > SELECT a,b,c FROM foo WHERE id = ?;
> >
> > except that the parameter has a name.
> >
> > Is there a way to write such a SELECT statement with the
> > named parameter in PostgreSQL?
> >
> > Thank you.
> >
> >

Re: How to write such a query?

[Ron]

On 1/6/22 1:06 AM, Igor Korot wrote:

Hi, Ron,

On Thu, Jan 6, 2022 at 1:01 AM Ron  wrote:

On 1/6/22 12:39 AM, Igor Korot wrote:

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Absolutely.  Of course, the exact method depends on the client.  Are you
referring to psql?

If so, then here's an example:
$ psql12 -v S=456789012 test
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

test=# select * from employee where ssn = :'S';
  ssn|  name   |  ssn_int
---+-+---
   456789012 | Fred Flintstone | 456789012
(1 row)

In this case, column ssn is of type varchar(9).

Is the syntax available since 9.0+? Or later?
I'm writing C++.


Like I said before... "the exact method depends on the client".  The C++ 
client */library/* is not the same as the psql *application*.


IOW, read the C+ client library documentation.

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

[Ron]

On 1/6/22 1:07 AM, Igor Korot wrote:

Hi, David,

On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston
 wrote:

On Wednesday, January 5, 2022, Igor Korot  wrote:


Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?


The server, and its prepared SQL capability, doesn’t understand named 
parameters.  Only numbered ones.

That said, there are a number of different ways to write and execute SQL 
available to you and each of those provides different extended capabilities.  
For instance, psql and its variables feature.

It looks like your answer contradicts with Ron...


TIMTOWTDI

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

[Igor Korot]

Hi, David,

On Thu, Jan 6, 2022 at 1:00 AM David G. Johnston
 wrote:
>
> On Wednesday, January 5, 2022, Igor Korot  wrote:
>>
>>
>> Is there a way to write such a SELECT statement with the
>> named parameter in PostgreSQL?
>
>
> The server, and its prepared SQL capability, doesn’t understand named 
> parameters.  Only numbered ones.
>
> That said, there are a number of different ways to write and execute SQL 
> available to you and each of those provides different extended capabilities.  
> For instance, psql and its variables feature.

It looks like your answer contradicts with Ron...

Thank you.

>
> David J.
>

Re: How to write such a query?

[Igor Korot]

Hi, Ron,

On Thu, Jan 6, 2022 at 1:01 AM Ron  wrote:
>
> On 1/6/22 12:39 AM, Igor Korot wrote:
> > Hi, ALL,
> > In SQLite you can write:
> >
> > SELECT a, b, c FROM foo WHERE id = :id;
> >
> > where ":id" is the named parameter.
> >
> > The query above is similar to
> >
> > SELECT a,b,c FROM foo WHERE id = ?;
> >
> > except that the parameter has a name.
> >
> > Is there a way to write such a SELECT statement with the
> > named parameter in PostgreSQL?
> Absolutely.  Of course, the exact method depends on the client.  Are you
> referring to psql?
>
> If so, then here's an example:
> $ psql12 -v S=456789012 test
> psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
> Type "help" for help.
>
> test=# select * from employee where ssn = :'S';
>  ssn|  name   |  ssn_int
> ---+-+---
>   456789012 | Fred Flintstone | 456789012
> (1 row)
>
> In this case, column ssn is of type varchar(9).

Is the syntax available since 9.0+? Or later?
I'm writing C++.

Thank you.

>
> --
> Angular momentum makes the world go 'round.
>
>

Re: How to write such a query?

[Ron]

On 1/6/22 12:39 AM, Igor Korot wrote:

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?
Absolutely.  Of course, the exact method depends on the client.  Are you 
referring to psql?


If so, then here's an example:
$ psql12 -v S=456789012 test
psql (12.8 (Ubuntu 12.8-1.pgdg18.04+1))
Type "help" for help.

test=# select * from employee where ssn = :'S';
    ssn    |  name   |  ssn_int
---+-+---
 456789012 | Fred Flintstone | 456789012
(1 row)

In this case, column ssn is of type varchar(9).

--
Angular momentum makes the world go 'round.

Re: How to write such a query?

[David G. Johnston]

On Wednesday, January 5, 2022, Igor Korot  wrote:
>
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>

The server, and its prepared SQL capability, doesn’t understand named
parameters.  Only numbered ones.

That said, there are a number of different ways to write and execute SQL
available to you and each of those provides different extended
capabilities.  For instance, psql and its variables feature.

David J.

Re: How to write such a query?

[Amul Sul]

See prepare statement : https://www.postgresql.org/docs/current/sql-prepare.html

On Thu, Jan 6, 2022 at 12:10 PM Igor Korot  wrote:
>
> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>
> Thank you.
>
>

How to write such a query?

[Igor Korot]

Hi, ALL,
In SQLite you can write:

SELECT a, b, c FROM foo WHERE id = :id;

where ":id" is the named parameter.

The query above is similar to

SELECT a,b,c FROM foo WHERE id = ?;

except that the parameter has a name.

Is there a way to write such a SELECT statement with the
named parameter in PostgreSQL?

Thank you.

Re: How to write such a query

[Francisco Olarte]

Igor:
> My problem is that I want to emulate Access behavior.
> As I said - Access does it without changing the query internally (I presume).
> I want to do the same with PostgreSQL.

Use access connected to Postgres.

> I'm just trying to understand how to make it work for any query
> I can have 3,4,5 tables, query them and then update the Nth record in the 
> resulting recordset.
> Access does it, PowerBuilder does it.

Now, jokes aside. Access, Powerbuilder are applications used to edit
databases ( among other things ).
Postgres is a database server which provides databases.

> I just want to understand how.

If you want to do the same thing in your app, they normally pull the
result of a query which some columns uniquely identifying the row, or
use a cursor and use the "WHERE CURRENT OFF" positioning. But YOUR APP
does it, postgres does no do it.

When Access does it the database is in postgres, or swl server, or
jet, or whatever. Access is not a database, in the sense postgres it.
Jet is.

Francisco Olarte.

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 1:18 PM, Igor Korot wrote:

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer > wrote:


 > How to find what the primary key (or UNIQUE identifier) value is
 > for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row
5". To
update the 5th record that you've fetched, you increment a
counter each time
you fetch a row, and when you read #5, do an UPDATE X SET field1
= 'blarg'
WHERE id = ;


It seems worth mentioning for benefit of the OPs question that there
_is_ a way to get a row number within a result set.  Understanding
and making good use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE
X.id = Y.id -- ORDER BY ?

That row number is going to depend on the order of the query, so it
might or might not have any meaning.  But if you queried with a
primary key and a row number, you could then tie the two together
and make an update based on that.


Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I 
presume).


I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in 
the resulting recordset.


You mean you are doing a join over 5 tables and then updating some 
record that is the output of the join?


If so are you updating all the values or a value or some portion of the 
values?


This is being done in a form or in the query builder?



Access does it, PowerBuilder does it.

I just want to understand how.

Thank you.


Cheers,
Ken
-- 
AGENCY Software

A Free Software data system
By and for non-profits
/http://agency-software.org//
/https://demo.agency-software.org/client/
ken.tan...@agency-software.org 
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Ken Tanzer]

On Fri, Sep 18, 2020 at 3:09 PM Igor Korot  wrote:

>
> Now one other little thing: could you point me to the documentation that
> explains the meaning of the "window function"?
>

Can I point you to Google instead?

https://www.google.com/search?q=postgresql+window+functions

Cheers,
Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: How to write such a query

[Igor Korot]

Ken,

On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer  wrote:

> On Fri, Sep 18, 2020 at 1:26 PM Ron  wrote:
>
>> On 9/18/20 3:18 PM, Igor Korot wrote:
>>
> Thank you for the info.
>> My problem is that I want to emulate Access behavior.
>>
>> As I said - Access does it without changing the query internally (I
>> presume).
>>
>> I want to do the same with PostgreSQL.
>>
>> I'm just trying to understand how to make it work for any query
>>
>> I can have 3,4,5 tables, query them and then update the Nth record in the
>> resulting recordset.
>>
>> Access does it, PowerBuilder does it.
>>
>> I just want to understand how.
>>
>>
>> They do it by hiding the details from you.
>>
>>
> That's true.  And Igor--people are asking you some good questions about
> why and design and such that you'd probably be well-advised to think about
> and respond to.
>
> So I'm not saying you should do this, but responding to your question
> specifically, and what the "details" are that Ron alludes to, one way to
> get the result you're asking about is to run your query adding on row
> numbers (pay attention to your ordering!), and then reference that result
> set from an update to get the primary key you want.  So I didn't test it,
> but something roughly like this:
>
> WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y
> WHERE X.id = Y.id ) UPDATE x SET ...  FROM tmp WHERE
>  tmp.row_number=5 AND x.field1=tmp.field1;
>

I didn't know that row_number() function exists and it is available across
different DBMSes.

I will test that query later.

Thank you.

Now one other little thing: could you point me to the documentation that
explains the meaning of the "window function"?



> Cheers,
> Ken
>
>
>
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: How to write such a query

[David G. Johnston]

On Fri, Sep 18, 2020 at 1:18 PM Igor Korot  wrote:

> As I said - Access does it without changing the query internally (I
> presume).
>
> I want to do the same with PostgreSQL.
>

I suspect they basically do the equivalent of:

UPDATE ... WHERE CURRENT OF ;

https://www.postgresql.org/docs/12/sql-update.html

David J.

Re: How to write such a query

[Ken Tanzer]

On Fri, Sep 18, 2020 at 1:26 PM Ron  wrote:

> On 9/18/20 3:18 PM, Igor Korot wrote:
>
Thank you for the info.
> My problem is that I want to emulate Access behavior.
>
> As I said - Access does it without changing the query internally (I
> presume).
>
> I want to do the same with PostgreSQL.
>
> I'm just trying to understand how to make it work for any query
>
> I can have 3,4,5 tables, query them and then update the Nth record in the
> resulting recordset.
>
> Access does it, PowerBuilder does it.
>
> I just want to understand how.
>
>
> They do it by hiding the details from you.
>
>
That's true.  And Igor--people are asking you some good questions about why
and design and such that you'd probably be well-advised to think about and
respond to.

So I'm not saying you should do this, but responding to your question
specifically, and what the "details" are that Ron alludes to, one way to
get the result you're asking about is to run your query adding on row
numbers (pay attention to your ordering!), and then reference that result
set from an update to get the primary key you want.  So I didn't test it,
but something roughly like this:

WITH tmp AS (SELECT X.field1, Y.field2,row_number() OVER () from X, Y WHERE
X.id = Y.id ) UPDATE x SET ...  FROM tmp WHERE
 tmp.row_number=5 AND x.field1=tmp.field1;

Cheers,
Ken



-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: How to write such a query

[Ron]

On 9/18/20 3:18 PM, Igor Korot wrote:

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer > wrote:


> How to find what the primary key (or UNIQUE identifier) value is
> for row 5 in the recordset?

You're missing the point: as mentioned before, there is no "row
5". To
update the 5th record that you've fetched, you increment a counter
each time
you fetch a row, and when you read #5, do an UPDATE X SET field1 =
'blarg'
WHERE id = ;


It seems worth mentioning for benefit of the OPs question that there
_is_ a way to get a row number within a result set.  Understanding and
making good use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()* from X, Y WHERE X.id
= Y.id -- ORDER BY ?

That row number is going to depend on the order of the query, so it
might or might not have any meaning. But if you queried with a primary
key and a row number, you could then tie the two together and make an
update based on that.


Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the 
resulting recordset.


Access does it, PowerBuilder does it.

I just want to understand how.


They do it by hiding the details from you.

--
Angular momentum makes the world go 'round.

Re: How to write such a query

[Thomas Kellerer]

Igor Korot schrieb am 18.09.2020 um 22:18:

Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the 
resulting recordset.

Access does it, PowerBuilder does it.


I assume that they query the database to find the primary key for the table in 
question.
Once the primary key columns are known (and part of the result), they generate 
the appropriate
UPDATE statement with a WHERE clause based on the the values in the result to 
update
the row that was changed.

Thomas

Re: How to write such a query

[Igor Korot]

Hi, Ken,

On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer  wrote:

> > How to find what the primary key (or UNIQUE identifier) value is
>> > for row 5 in the recordset?
>>
>> You're missing the point: as mentioned before, there is no "row 5". To
>> update the 5th record that you've fetched, you increment a counter each
>> time
>> you fetch a row, and when you read #5, do an UPDATE X SET field1 =
>> 'blarg'
>> WHERE id = ;
>>
>>
> It seems worth mentioning for benefit of the OPs question that there _is_
> a way to get a row number within a result set.  Understanding and making
> good use of that is an additional matter.
>
> SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE X.id =
> Y.id -- ORDER BY ?
>
> That row number is going to depend on the order of the query, so it might
> or might not have any meaning.  But if you queried with a primary key and a
> row number, you could then tie the two together and make an update based on
> that.
>

Thank you for the info.
My problem is that I want to emulate Access behavior.

As I said - Access does it without changing the query internally (I
presume).

I want to do the same with PostgreSQL.

I'm just trying to understand how to make it work for any query

I can have 3,4,5 tables, query them and then update the Nth record in the
resulting recordset.

Access does it, PowerBuilder does it.

I just want to understand how.

Thank you.


> Cheers,
> Ken
> --
> AGENCY Software
> A Free Software data system
> By and for non-profits
> *http://agency-software.org/ *
> *https://demo.agency-software.org/client
> *
> ken.tan...@agency-software.org
> (253) 245-3801
>
> Subscribe to the mailing list
>  to
> learn more about AGENCY or
> follow the discussion.
>

Re: How to write such a query

[Rob Sargent]

> On Sep 18, 2020, at 1:45 PM, Ken Tanzer  wrote:
> 
> > How to find what the primary key (or UNIQUE identifier) value is
> > for row 5 in the recordset?
> 
> You're missing the point: as mentioned before, there is no "row 5". To 
> update the 5th record that you've fetched, you increment a counter each time 
> you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg' 
> WHERE id = ;
> 
> 
> It seems worth mentioning for benefit of the OPs question that there _is_ a 
> way to get a row number within a result set.  Understanding and making good 
> use of that is an additional matter.
> 
> SELECT X.field1, Y.field2,row_number() OVER ()  from X, Y WHERE X.id = Y.id 
> -- ORDER BY ?
> 
> That row number is going to depend on the order of the query, so it might or 
> might not have any meaning.  But if you queried with a primary key and a row 
> number, you could then tie the two together and make an update based on that.

If “row 5” as seen by the OP has no distinguishing characteristic directing OP 
to edit that tuple then he’s in a world of hurt, well beyond the reach of 
anyone here.

Re: How to write such a query

[Ken Tanzer]

>
> > How to find what the primary key (or UNIQUE identifier) value is
> > for row 5 in the recordset?
>
> You're missing the point: as mentioned before, there is no "row 5". To
> update the 5th record that you've fetched, you increment a counter each
> time
> you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg'
> WHERE id = ;
>
>
It seems worth mentioning for benefit of the OPs question that there _is_ a
way to get a row number within a result set.  Understanding and making good
use of that is an additional matter.

SELECT X.field1, Y.field2*,row_number() OVER ()*  from X, Y WHERE X.id =
Y.id -- ORDER BY ?

That row number is going to depend on the order of the query, so it might
or might not have any meaning.  But if you queried with a primary key and a
row number, you could then tie the two together and make an update based on
that.

Cheers,
Ken
-- 
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ *
*https://demo.agency-software.org/client
*
ken.tan...@agency-software.org
(253) 245-3801

Subscribe to the mailing list
 to
learn more about AGENCY or
follow the discussion.

Re: How to write such a query

[Ron]

On 9/18/20 1:49 PM, Igor Korot wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
mailto:jonathanrstr...@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're missing the point: as mentioned before, there is no "row 5". To 
update the 5th record that you've fetched, you increment a counter each time 
you fetch a row, and when you read #5, do an UPDATE X SET field1 = 'blarg' 
WHERE id = ;


--
Angular momentum makes the world go 'round.

Re: How to write such a query

[Jonathan Strong]

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




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 3:08 PM Adrian Klaver 
wrote:

> On 9/18/20 11:49 AM, Igor Korot wrote:
> > Hi, Adrian,
> >
> > On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
> >  wrote:
> >>
> >> On 9/18/20 10:46 AM, Igor Korot wrote:
> >>> Hi, Johnathan,
> >>>
> >>> On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> >>> mailto:jonathanrstr...@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.kla...@aklaver.com
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 11:49 AM, Igor Korot wrote:

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:


On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
mailto:jonathanrstr...@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.kla...@aklaver.com



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Igor Korot]

Hi, Adrian,

On Fri, Sep 18, 2020 at 12:58 PM Adrian Klaver
 wrote:
>
> On 9/18/20 10:46 AM, Igor Korot wrote:
> > Hi, Johnathan,
> >
> > On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> > mailto:jonathanrstr...@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?

Thank you.

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

Re: How to write such a query

[Jonathan Strong]

Yes...absolutely. Short of using ORDER BY, the order of a multi-row result
set can be arbitrary, with "row position" having no significant meaning.

This gets back to understanding set theory, the relational model, the
various types of keys (primary, candidate, foreign, etc.). Truly crucial to
understand the model in order to write correctly functioning and reliable
code.

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer  wrote:

> Igor Korot schrieb am 18.09.2020 um 19:29:
> > [code]
> > 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));
> > SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> > [/code]
> >
> > Assuming that the SELECT return 10 rows, I want to update X.field1
> > in row 5.
>
> There is no such thing as "row 5" in a relational database.
>
> Rows in a table have no inherent sort order. The only way you can identify
> a row, is by the value of its primary (or unique) key. Not by "position".
>
> The only way you can identify "row 5" is, if you use an ORDER BY to
> define a sort order on the result - but that position is only valid
> for that _result_, it has no meaning for the actual table data.
>
> Which brings us back to the fact, that the only way to (uniquely) identify
> a row in a table is to specify its primary key value in the WHERE clause
>
>
>
>
>
>

Re: How to write such a query

[Jonathan Strong]

Yes - 100%

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 2:22 PM Adrian Klaver 
wrote:

> On 9/18/20 11:13 AM, Jonathan Strong wrote:
> > @Adrian -
> >
> > Using a unique key value or otherwise isolating a specific record via
> > selection against values in its attributes is certainly preferable to
> > choosing a row to update via its position in a result set, unless the
> > use case actually makes use of that position info as a meaningful
> > descriptor of the data in some fashion.
>
>
> The bigger issue is deciding what attribute of the selected row is be
> used to do the UPDATE. Unless it is the PRIMARY KEY(or other UNIQUE
> key(s)) then you very likely are going to UPDATE more then you bargained
> for.
>
> >
> > - Jon
> >
> >  <
> https://www.jonathanrstrong.com>
> >
> >
> >
> > *Jonathan Strong*
> >
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 11:13 AM, Jonathan Strong wrote:

@Adrian -

Using a unique key value or otherwise isolating a specific record via 
selection against values in its attributes is certainly preferable to 
choosing a row to update via its position in a result set, unless the 
use case actually makes use of that position info as a meaningful 
descriptor of the data in some fashion.



The bigger issue is deciding what attribute of the selected row is be 
used to do the UPDATE. Unless it is the PRIMARY KEY(or other UNIQUE 
key(s)) then you very likely are going to UPDATE more then you bargained 
for.




- Jon

 



*Jonathan Strong*




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Thomas Kellerer]

Igor Korot schrieb am 18.09.2020 um 19:29:

[code]
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));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.


There is no such thing as "row 5" in a relational database.

Rows in a table have no inherent sort order. The only way you can identify
a row, is by the value of its primary (or unique) key. Not by "position".

The only way you can identify "row 5" is, if you use an ORDER BY to
define a sort order on the result - but that position is only valid
for that _result_, it has no meaning for the actual table data.

Which brings us back to the fact, that the only way to (uniquely) identify
a row in a table is to specify its primary key value in the WHERE clause

Re: How to write such a query

[Jonathan Strong]

@Adrian -

Using a unique key value or otherwise isolating a specific record via
selection against values in its attributes is certainly preferable to
choosing a row to update via its position in a result set, unless the use
case actually makes use of that position info as a meaningful descriptor of
the data in some fashion.

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 1:58 PM Adrian Klaver 
wrote:

> On 9/18/20 10:46 AM, Igor Korot wrote:
> > Hi, Johnathan,
> >
> > On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong
> > mailto:jonathanrstr...@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.
>
> > Thank you.
> >
>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: How to write such a query

[Adrian Klaver]

On 9/18/20 10:46 AM, Igor Korot wrote:

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong 
mailto:jonathanrstr...@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.



Thank you.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: How to write such a query

[Igor Korot]

Hi, Johnathan,

On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong 
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?

Thank you.


> - Jon
>
> 
> 
>
> *Jonathan Strong*
>
> CIO / CTO / Consultant
>
> *P:* 609-532-1715 *E:* jonathanrstr...@gmail.com
>
> *Quora Top Writer *
>
>
> On Fri, Sep 18, 2020 at 1:27 PM Igor Korot  wrote:
>
>> Hi,
>> Consider following
>>
>> [code]
>> 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));
>> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
>> [/code]
>>
>> Assuming that the SELECT return 10 rows, I want to update X.field1
>> in row 5.
>>
>> How do I write a WHERE clause in the
>>
>> [code]
>> UPDATE X.field1 SET X.field1 = '' WHERE
>> [/code]
>>
>> Thank you.
>>
>>
>>

Re: How to write such a query

[Igor Korot]

Hi, Paul

On Fri, Sep 18, 2020 at 12:34 PM Paul Förster  wrote:
>
> Hi Igor,
>
> > On 18. Sep, 2020, at 19:29, Igor Korot  wrote:
> >
> > Hi,
> > Consider following
> >
> > [code]
> > 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));
> > SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> > [/code]
> >
> > Assuming that the SELECT return 10 rows, I want to update X.field1
> > in row 5.
> >
> > How do I write a WHERE clause in the
> >
> > [code]
> > UPDATE X.field1 SET X.field1 = '' WHERE
> > [/code]
> >
> > Thank you.
>
> update x set field1='' where id=5;

How do you know that the row #5 will have an X.id field 5?

Thank you.

>
> Cheers,
> Paul

Re: How to write such a query

[Paul Förster]

Hi Igor,

> On 18. Sep, 2020, at 19:29, Igor Korot  wrote:
> 
> Hi,
> Consider following
> 
> [code]
> 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));
> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> [/code]
> 
> Assuming that the SELECT return 10 rows, I want to update X.field1
> in row 5.
> 
> How do I write a WHERE clause in the
> 
> [code]
> UPDATE X.field1 SET X.field1 = '' WHERE
> [/code]
> 
> Thank you.

update x set field1='' where id=5;

Cheers,
Paul

Re: How to write such a query

[Jonathan Strong]

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?

- Jon




*Jonathan Strong*

CIO / CTO / Consultant

*P:* 609-532-1715 *E:* jonathanrstr...@gmail.com

*Quora Top Writer *


On Fri, Sep 18, 2020 at 1:27 PM Igor Korot  wrote:

> Hi,
> Consider following
>
> [code]
> 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));
> SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
> [/code]
>
> Assuming that the SELECT return 10 rows, I want to update X.field1
> in row 5.
>
> How do I write a WHERE clause in the
>
> [code]
> UPDATE X.field1 SET X.field1 = '' WHERE
> [/code]
>
> Thank you.
>
>
>

How to write such a query

[Igor Korot]

Hi,
Consider following

[code]
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));
SELECT X.field1, Y.field2 from X, Y WHERE X.id = Y.id;
[/code]

Assuming that the SELECT return 10 rows, I want to update X.field1
in row 5.

How do I write a WHERE clause in the

[code]
UPDATE X.field1 SET X.field1 = '' WHERE
[/code]

Thank you.