Thread: How to write such a query
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?
On Fri, Sep 18, 2020 at 1:27 PM Igor Korot <ikorot01@gmail.com> 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 = '<some_string>' WHERE....
[/code]
Thank you.
Hi, Johnathan,
On Fri, Sep 18, 2020 at 12:34 PM Jonathan Strong <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?
Thank you.
On Fri, Sep 18, 2020 at 1:27 PM Igor Korot <ikorot01@gmail.com> 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 = '<some_string>' WHERE....
[/code]
Thank you.
@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.
On Fri, Sep 18, 2020 at 1: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.
> Thank you.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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.
On Fri, Sep 18, 2020 at 2:17 PM Thomas Kellerer <shammat@gmx.net> 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
On Fri, Sep 18, 2020 at 2:22 PM Adrian Klaver <adrian.klaver@aklaver.com> 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.linkedin.com/in/jonstrong/> <https://www.jonathanrstrong.com>
>
>
>
> *Jonathan Strong*
>
--
Adrian Klaver
adrian.klaver@aklaver.com
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/
On Fri, Sep 18, 2020 at 3:08 PM Adrian Klaver <adrian.klaver@aklaver.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.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
> 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 = <thekeyvalue>;
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
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Hi, Ken,
On Fri, Sep 18, 2020 at 2:46 PM Ken Tanzer <ken.tanzer@gmail.com> 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 = <thekeyvalue>;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 SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
On Fri, Sep 18, 2020 at 1:26 PM Ron <ronljohnsonjr@gmail.com> 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 queryI 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;
tmp.row_number=5 AND x.field1=tmp.field1;
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
Ken,
On Fri, Sep 18, 2020 at 3:35 PM Ken Tanzer <ken.tanzer@gmail.com> wrote:
On Fri, Sep 18, 2020 at 1:26 PM Ron <ronljohnsonjr@gmail.com> 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 queryI 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 SoftwareA Free Software data systemBy and for non-profits(253) 245-3801learn more about AGENCY orfollow the discussion.
On Fri, Sep 18, 2020 at 3:09 PM Igor Korot <ikorot01@gmail.com> 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?
Cheers,
Ken
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.