Update statement

classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Update statement

mailing lists
Hi,

How do I create this kind of update statement?

UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);

The RHS should always be used with the values of a and b before the assignment.

I think that the result of this kind of statement is undefined, or?

Regards,
Hartwig


_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

Simon Slavin-3

On 6 May 2013, at 11:48pm, skywind mailing lists <[hidden email]> wrote:

> How do I create this kind of update statement?
>
> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
>
> The RHS should always be used with the values of a and b before the assignment.
>
> I think that the result of this kind of statement is undefined, or?

No need to worry, it will work the way you want it to work:

The row is read.
The new values are calculated.
The new values are written to the database.

This is standard in all SQL implementations.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

Simon Slavin-3

On 6 May 2013, at 11:53pm, Simon Slavin <[hidden email]> wrote:

> On 6 May 2013, at 11:48pm, skywind mailing lists <[hidden email]> wrote:
>
>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
>>
>> The RHS should always be used with the values of a and b before the assignment.
>>
>> I think that the result of this kind of statement is undefined, or?
>
> No need to worry, it will work the way you want it to work:
>
> The row is read.
> The new values are calculated.
> The new values are written to the database.

Okay, could everyone please forget that one ?  That was a terrible description.  As far as I know my statement is correct.  The values used in the calculations are the values read from the row, not the values SQL is assembling to write back to the row.  To make up for my poor explanation please accept this demonstration:

sqlite> CREATE TABLE x (a,b);
sqlite> INSERT INTO x VALUES (1,100);
sqlite> SELECT * FROM x;
1|100
sqlite> UPDATE x SET a=b,b=a;
sqlite> SELECT * FROM x;
100|1
sqlite> UPDATE x SET b=a,a=b;
sqlite> SELECT * FROM x;
1|100
sqlite> UPDATE x SET a=999,b=a;
sqlite> SELECT * FROM x;
999|1
sqlite> UPDATE x SET b=a,a=777;
sqlite> SELECT * FROM x;
777|999
sqlite>

My understanding is that this is part of the SQL standard, though I can't find it spelled out anywhere right now.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

James K. Lowden
In reply to this post by Simon Slavin-3
On Mon, 6 May 2013 23:53:40 +0100
Simon Slavin <[hidden email]> wrote:

> > How do I create this kind of update statement?
> >
> > UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
> >
> > The RHS should always be used with the values of a and b before the
> > assignment.
> >
> > I think that the result of this kind of statement is undefined, or?
>
> No need to worry, it will work the way you want it to work:
>
> The row is read.
> The new values are calculated.
> The new values are written to the database.
...
> That was a terrible description.

Actually that's not a bad approximation of what happens.  Here's a
simpler example:

sqlite> create table t(a int, b int);
sqlite> insert into t values (1,2);
sqlite> select * from t;
a           b        
----------  ----------
1           2        
sqlite> update t set a=b, b=a;  -- Et Voila!
sqlite> select * from t;
a           b        
----------  ----------
2           1        

There is no "RHS".  The syntax and semantics of SQL are its own; they
cannot be extrapolated from other languages.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

mailing lists
Hi,

my question is: is it guaranteed that it works?

Regards,
Hartwig

Am 07.05.2013 um 03:24 schrieb James K. Lowden:

> On Mon, 6 May 2013 23:53:40 +0100
> Simon Slavin <[hidden email]> wrote:
>
>>> How do I create this kind of update statement?
>>>
>>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
>>>
>>> The RHS should always be used with the values of a and b before the
>>> assignment.
>>>
>>> I think that the result of this kind of statement is undefined, or?
>>
>> No need to worry, it will work the way you want it to work:
>>
>> The row is read.
>> The new values are calculated.
>> The new values are written to the database.
> ...
>> That was a terrible description.
>
> Actually that's not a bad approximation of what happens.  Here's a
> simpler example:
>
> sqlite> create table t(a int, b int);
> sqlite> insert into t values (1,2);
> sqlite> select * from t;
> a           b        
> ----------  ----------
> 1           2        
> sqlite> update t set a=b, b=a;  -- Et Voila!
> sqlite> select * from t;
> a           b        
> ----------  ----------
> 2           1        
>
> There is no "RHS".  The syntax and semantics of SQL are its own; they
> cannot be extrapolated from other languages.  

I know but everybody knows what I meant, or? And its a quite brief description.

>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

Peter Aronson-3
At least for SQLite it appears to be.  From the Update doc page
(http://www.sqlite.org/lang_update.html):

"The modifications made to each row affected by an UPDATE statement are
determined by the list of assignments following the SET keyword. Each assignment
specifies a column name to the left of the equals sign and a scalar expression
to the right. For each affected row, the named columns are set to the values
found by evaluating the corresponding scalar expressions. If a single
column-name appears more than once in the list of assignment expressions, all
but the rightmost occurrence is ignored. Columns that do not appear in the list
of assignments are left unmodified. The scalar expressions may refer to columns
of the row being updated. In this case all scalar expressions are evaluated
before any assignments are made."

And in fact I rely on it behaving this way for in one place in my SQLite code
and it seems to be working correctly.

Peter

----- Original Message ----

> From: skywind mailing lists <[hidden email]>
> To: General Discussion of SQLite Database <[hidden email]>
> Sent: Tue, May 7, 2013 10:46:42 AM
> Subject: Re: [sqlite] Update statement
>
> Hi,
>
> my question is: is it guaranteed that it works?
>
> Regards,
> Hartwig
>
> Am 07.05.2013 um 03:24 schrieb James K. Lowden:
>
> > On Mon, 6 May 2013 23:53:40 +0100
> > Simon Slavin <[hidden email]> wrote:
> >
> >>> How do I create this kind of update statement?
> >>>
> >>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
> >>>
> >>> The RHS should always be used with the values of a and b before the
> >>> assignment.
> >>>
> >>> I think that the result of this kind of statement is undefined, or?
> >>
> >> No need to worry, it will work the way you want it to work:
> >>
> >> The row is read.
> >> The new values are calculated.
> >> The new values are written to the database.
> > ...
> >> That was a terrible description.
> >
> > Actually that's not a bad approximation of what happens.  Here's a
> > simpler example:
> >
> > sqlite> create table t(a int, b int);
> > sqlite> insert into t values (1,2);
> > sqlite> select * from t;
> > a          b       
> > ----------  ----------
> > 1          2       
> > sqlite> update t set a=b, b=a;  -- Et Voila!
> > sqlite> select * from t;
> > a          b       
> > ----------  ----------
> > 2          1       
> >
> > There is no "RHS".  The syntax and semantics of SQL are its own; they
> > cannot be extrapolated from other languages. 
>
> I know but everybody knows what I meant, or? And its a quite brief
description.

>
> >
> > --jkl
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

Luuk
In reply to this post by mailing lists
On 07-05-2013 19:46, skywind mailing lists wrote:
> Hi,
>
> my question is: is it guaranteed that it works?
>
> Regards,
> Hartwig
>

http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt
chapter:  13.9 <update statement: positioned>
  6) The <value expression>s are effectively evaluated before updat-
             ing the object row. If a <value expression> contains a
reference
             to a column of T, then the reference is to the value of that
             column in the object row before any value of the object row is
             updated.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

Simon Slavin-3

On 7 May 2013, at 8:04pm, Luuk <[hidden email]> wrote:

> http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt
> chapter:  13.9 <update statement: positioned>
> 6) The <value expression>s are effectively evaluated before updat-
>            ing the object row. If a <value expression> contains a reference
>            to a column of T, then the reference is to the value of that
>            column in the object row before any value of the object row is
>            updated.

Thank you Luuk.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Update statement

mailing lists
In reply to this post by Peter Aronson-3
Hi Peter,

thanks.

But it does not seem to work in my environment. Probably somewhere a bug on my side.

Hartwig

Am 07.05.2013 um 19:55 schrieb Peter Aronson:

> At least for SQLite it appears to be.  From the Update doc page
> (http://www.sqlite.org/lang_update.html):
>
> "The modifications made to each row affected by an UPDATE statement are
> determined by the list of assignments following the SET keyword. Each assignment
> specifies a column name to the left of the equals sign and a scalar expression
> to the right. For each affected row, the named columns are set to the values
> found by evaluating the corresponding scalar expressions. If a single
> column-name appears more than once in the list of assignment expressions, all
> but the rightmost occurrence is ignored. Columns that do not appear in the list
> of assignments are left unmodified. The scalar expressions may refer to columns
> of the row being updated. In this case all scalar expressions are evaluated
> before any assignments are made."
>
> And in fact I rely on it behaving this way for in one place in my SQLite code
> and it seems to be working correctly.
>
> Peter
>
> ----- Original Message ----
>> From: skywind mailing lists <[hidden email]>
>> To: General Discussion of SQLite Database <[hidden email]>
>> Sent: Tue, May 7, 2013 10:46:42 AM
>> Subject: Re: [sqlite] Update statement
>>
>> Hi,
>>
>> my question is: is it guaranteed that it works?
>>
>> Regards,
>> Hartwig
>>
>> Am 07.05.2013 um 03:24 schrieb James K. Lowden:
>>
>>> On Mon, 6 May 2013 23:53:40 +0100
>>> Simon Slavin <[hidden email]> wrote:
>>>
>>>>> How do I create this kind of update statement?
>>>>>
>>>>> UPDATE T SET a=0.5*(a+b), b=0.5*(b-a);
>>>>>
>>>>> The RHS should always be used with the values of a and b before the
>>>>> assignment.
>>>>>
>>>>> I think that the result of this kind of statement is undefined, or?
>>>>
>>>> No need to worry, it will work the way you want it to work:
>>>>
>>>> The row is read.
>>>> The new values are calculated.
>>>> The new values are written to the database.
>>> ...
>>>> That was a terrible description.
>>>
>>> Actually that's not a bad approximation of what happens.  Here's a
>>> simpler example:
>>>
>>> sqlite> create table t(a int, b int);
>>> sqlite> insert into t values (1,2);
>>> sqlite> select * from t;
>>> a          b        
>>> ----------  ----------
>>> 1          2        
>>> sqlite> update t set a=b, b=a;  -- Et Voila!
>>> sqlite> select * from t;
>>> a          b        
>>> ----------  ----------
>>> 2          1        
>>>
>>> There is no "RHS".  The syntax and semantics of SQL are its own; they
>>> cannot be extrapolated from other languages.  
>>
>> I know but everybody knows what I meant, or? And its a quite brief
> description.
>>
>>>
>>> --jkl
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users