UPDATE statement without FROM clause

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

UPDATE statement without FROM clause

skywind mailing lists
Hi,

I am using quite often SQL statements that update the data of one table with data from another table. This leads to some quite complex (and slow) statements because SQLite3 is not supporting a FROM clause in update statements. I am just wondering why the FROM clause is not supported by SQLite3?! Is this too complex to implement or is there simply no demand for these type of statements?

Regards,
Hartwig



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

Re: UPDATE statement without FROM clause

Gerry Snyder-4
If SQLite implemented the FROM it would just be a translation into the
complex and slow statements you want to avoid.

Gerry Snyder
On Jun 4, 2016 9:19 AM, "skywind mailing lists" <[hidden email]>
wrote:

> Hi,
>
> I am using quite often SQL statements that update the data of one table
> with data from another table. This leads to some quite complex (and slow)
> statements because SQLite3 is not supporting a FROM clause in update
> statements. I am just wondering why the FROM clause is not supported by
> SQLite3?! Is this too complex to implement or is there simply no demand for
> these type of statements?
>
> Regards,
> Hartwig
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE statement without FROM clause

skywind mailing lists
Hi,

why? At the moment I have to run something like:

UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

Using a FROM clause I just need one scan through B (at least in principle). Now, I need N+1 scans.

Regards,
Hartwig

> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <[hidden email]>:
>
> If SQLite implemented the FROM it would just be a translation into the
> complex and slow statements you want to avoid.
>
> Gerry Snyder
> On Jun 4, 2016 9:19 AM, "skywind mailing lists" <[hidden email]>
> wrote:
>
>> Hi,
>>
>> I am using quite often SQL statements that update the data of one table
>> with data from another table. This leads to some quite complex (and slow)
>> statements because SQLite3 is not supporting a FROM clause in update
>> statements. I am just wondering why the FROM clause is not supported by
>> SQLite3?! Is this too complex to implement or is there simply no demand for
>> these type of statements?
>>
>> Regards,
>> Hartwig
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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

Re: UPDATE statement without FROM clause

Brad Stiles-2
Is there some absolute requirement that it all be done in SQL?  Depending on the number of "items", it'd probably be faster in a loop in code.

Even in MSSQL Server using TSQL, you're better off using a cursor for that sort of thing. I only use UPDATE FROM when I need a join to formulate the WHERE clause.

> On Jun 4, 2016, at 12:18, skywind mailing lists <[hidden email]> wrote:
>
> Hi,
>
> why? At the moment I have to run something like:
>
> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
>
> Using a FROM clause I just need one scan through B (at least in principle). Now, I need N+1 scans.
>
> Regards,
> Hartwig
>
>> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <[hidden email]>:
>>
>> If SQLite implemented the FROM it would just be a translation into the
>> complex and slow statements you want to avoid.
>>
>> Gerry Snyder
>> On Jun 4, 2016 9:19 AM, "skywind mailing lists" <[hidden email]>
>> wrote:
>>
>>> Hi,
>>>
>>> I am using quite often SQL statements that update the data of one table
>>> with data from another table. This leads to some quite complex (and slow)
>>> statements because SQLite3 is not supporting a FROM clause in update
>>> statements. I am just wondering why the FROM clause is not supported by
>>> SQLite3?! Is this too complex to implement or is there simply no demand for
>>> these type of statements?
>>>
>>> Regards,
>>> Hartwig
>>>
>>>
>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> [hidden email]
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE statement without FROM clause

Jean-Christophe Deschamps-3
In reply to this post by skywind mailing lists
At 18:18 04/06/2016, you wrote:

>Hi,
>
>why? At the moment I have to run something like:
>
>UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),...
>itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
>
>Using a FROM clause I just need one scan through B (at least in
>principle). Now, I need N+1 scans.
>
>Regards,
>Hartwig

Can't the same update be done more efficiently with a CTE?

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

Re: UPDATE statement without FROM clause

Simon Slavin-3
In reply to this post by skywind mailing lists

On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps <[hidden email]> wrote:

> Can't the same update be done more efficiently with a CTE?

The command inside the WITH has to be a SELECT command.

I wonder if there's a good reason for that.  If the command inside WITH could make changes to the database the result might be ambiguous, and very sensitive to how the SQL engine works.

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

Re: UPDATE statement without FROM clause

R Smith


On 2016/06/04 11:34 PM, Simon Slavin wrote:
> On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps <[hidden email]> wrote:
>
>> Can't the same update be done more efficiently with a CTE?
> The command inside the WITH has to be a SELECT command.
>
> I wonder if there's a good reason for that.  If the command inside WITH could make changes to the database the result might be ambiguous, and very sensitive to how the SQL engine works.

Well yes, WITH is a form of SELECT, so the function clause inside the
WITH has to be SELECT, in the same way that the function clause inside
an UPDATE statement has to be SET. However, the result of the WITH can
be applied to any Insert or Update or such (wherever a SELECT can be
used) - to demonstrate:


       -- SQLite version 3.9.2  [ Release: 2015-11-02 ]  on  SQLitespeed
    version 2.0.2.4.

       -- Script Items: 5          Parameter Count: 0
       -- 2016-06-05 02:26:52.452  |  [Info] Script Initialized, Started
    executing...
       --
    ================================================================================================

    CREATE TABLE Testy(
       a INT,
       b INT
    );

    WITH CTE(x) AS (
         SELECT 1
       UNION ALL
         SELECT x+1 FROM CTE WHERE x<10
    )
    INSERT INTO Testy(a,b)
    SELECT x,x*2 FROM CTE;


    SELECT * FROM Testy;


       --       a      |       b
       -- ------------ | ------------
       --       1      |       2
       --       2      |       4
       --       3      |       6
       --       4      |       8
       --       5      |      10
       --       6      |      12
       --       7      |      14
       --       8      |      16
       --       9      |      18
       --      10      |      20

    WITH CTE(x,y) AS (
         SELECT 1,100
       UNION ALL
         SELECT x+1,y+(x*5) FROM CTE WHERE x<10
    )
    UPDATE Testy SET b=(SELECT CTE.y FROM CTE WHERE CTE.x = Testy.a
    LIMIT 1) WHERE Testy.a < 8


    SELECT * FROM Testy;


       --       a      |   b
       -- ------------ | -----
       --       1      |  100
       --       2      |  105
       --       3      |  115
       --       4      |  130
       --       5      |  150
       --       6      |  175
       --       7      |  205
       --       8      |   16
       --       9      |   18
       --      10      |   20


As to the OP's question of whether it can be done in a CTE (I assume he
means /using/ a CTE, and not necessarily having the working bits
/inside/ the CTE), there still is no way to use the UPDATE-FROM, even
from a CTE.

I might be wrong, but I think there is a problem with the UPDATE FROM
implementation; it has to require a scan one way or the other. Whether
you look up the value in the referenced table according to the key in
the updated table, or vice versa, there is no single-pass way of
avoiding the per-item lookup (unless somebody has done this - I'd be
very interested how). There is no way even for the QP to be sure a Key
column from both (or even one) of the tables will be involved in the
WHERE clause.
Perhaps if you have two tables that are exactly in sync (same Key-list,
same length), but that must be a serious edge-case.

The only advantage using the UPDATE-FROM syntax, which I can see, is
human legibility in SQL terms - which is not nothing btw.


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

Re: UPDATE statement without FROM clause

Jean-Christophe Deschamps-3
In reply to this post by skywind mailing lists
At 23:34 04/06/2016, you wrote:

>On 4 Jun 2016, at 10:15pm, Jean-Christophe Deschamps
><[hidden email]> wrote:
>
> > Can't the same update be done more efficiently with a CTE?
>
>The command inside the WITH has to be a SELECT command.

Definitely not as Ryan pointed out, and as the help file clearly states
otherwise:

     "All common table expressions (ordinary and recursive) are created by
      prepending a WITH clause in front of a
<http://www.sqlite.org/lang_select.html>SELECT,
<http://www.sqlite.org/lang_insert.html>INSERT,
<http://www.sqlite.org/lang_delete.html>DELETE, or
<http://www.sqlite.org/lang_update.html>UPDATE
      statement."

I agree that in that simple example, using a CTE is just syntactic sugar:

CREATE TABLE A (
   Id INTEGER NOT NULL PRIMARY KEY,
   Item1 INT,
   Item2 CHAR,
   item3 CHAR);
with ints (n) as (select -5000 union all select n+1 from ints limit 10000)
INSERT INTO A (id) select n from ints;

CREATE TABLE B (
   Id INTEGER NOT NULL PRIMARY KEY,
   Item1 INT,
   Item2 CHAR,
   item3 CHAR);
INSERT INTO B (item1) VALUES
(83),(81),(76),(105),(116),(101),(32),(114),(111),(99),(107),(115),(33),(0);
-- make table B bigger (IDs in A and B only partl overlap)
with ints (n) as (select (select count(*) from B)+1 union all select
n+1 from ints limit 10000)
INSERT INTO B (id) select n from ints;

with C as (select id, item1, item2, item3 from B where item1 not null)
update A set
        item1 = (select C.item1 from C where C.id = A.id),
        item2 = (select char(C.item1) from C where C.id = A.id),
        item3 = (select group_concat(item2, '') from A AA where AA.id
<= A.id)
where id in (select id from C);

select * from A where item2 not null;

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

Re: UPDATE statement without FROM clause

Keith Medcalf
In reply to this post by skywind mailing lists

You can simulate either a two-pass or one-pass UPDATE <table> SET ... FROM <table>, <more tables ...> WHERE <conditions to generate update set>

By doing one or the other of the following (depending on whether you want one-pass or two-pass).

for a one-pass update:

BEGIN IMMEDIATE;
SELECT <table>.rowid, <SET variables> FROM <tables ...> WHERE <join conditions>
fetch a row
  UPDATE <table> SET x=?, ...  WHERE rowid=? -- bind the result set to the parameters and execute
when you run out of rows,
COMMIT;

For a two pass update BEGIN IMMEDIATE then either (a) SELECT into a temp table and do the update above from that table, or (b) store the rows in memory and then do the update afterwards.  If you used a temp table, drop it before committing.

> I am using quite often SQL statements that update the data of one table
> with data from another table. This leads to some quite complex (and slow)
> statements because SQLite3 is not supporting a FROM clause in update
> statements. I am just wondering why the FROM clause is not supported by
> SQLite3?! Is this too complex to implement or is there simply no demand
> for these type of statements?




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

Re: UPDATE statement without FROM clause

Hick Gunter
In reply to this post by skywind mailing lists
Would not

Replace into A (<fieldlist>) select <key_fields>,<unchanged_fields>, <changed_fields> from A [cross] join B on( <key_fields>);

do the trick? If a.rowid has an alias (i.e. integer primary key) then the modified rows would be deleted, but reinserted with their respective previous rowids. This may required switching foreign keys off fort he duration of the update.

-----Ursprüngliche Nachricht-----
Von: [hidden email] [mailto:[hidden email]] Im Auftrag von skywind mailing lists
Gesendet: Samstag, 04. Juni 2016 18:19
An: SQLite mailing list <[hidden email]>
Betreff: Re: [sqlite] UPDATE statement without FROM clause

Hi,

why? At the moment I have to run something like:

UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

Using a FROM clause I just need one scan through B (at least in principle). Now, I need N+1 scans.

Regards,
Hartwig

> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <[hidden email]>:
>
> If SQLite implemented the FROM it would just be a translation into the
> complex and slow statements you want to avoid.
>
> Gerry Snyder
> On Jun 4, 2016 9:19 AM, "skywind mailing lists"
> <[hidden email]>
> wrote:
>
>> Hi,
>>
>> I am using quite often SQL statements that update the data of one
>> table with data from another table. This leads to some quite complex
>> (and slow) statements because SQLite3 is not supporting a FROM clause
>> in update statements. I am just wondering why the FROM clause is not
>> supported by SQLite3?! Is this too complex to implement or is there
>> simply no demand for these type of statements?
>>
>> Regards,
>> Hartwig
>>
>>
>>
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


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

Re: UPDATE statement without FROM clause

Graham Holden
One idea I came up with a while ago is to use a trigger on the "FROM"
table to cause updates to the main table:

CREATE TRIGGER UpdateTrigger AFTER UPDATE OF TriggerField ON Updates
BEGIN
    UPDATE Master SET
        Field1 = OLD.Field1,
        Field2 = OLD.Field2,
        ...
    WHERE Master.Key = OLD.Key
END;

and trigger the update with:

UPDATE Updates SET TriggerField = NULL ;

It seems to run (in my very simple test) at the same speed as REPLACE
INTO but has the advantages that (a) it doesn't replace rows (possibly
affecting rowids) and (b) not having to specify unchanged fields.

See http://stackoverflow.com/a/22481731/2096401 for more details.

Graham Holden
[hidden email]


Monday, June 06, 2016, 9:11:14 AM, Hick Gunter <[hidden email]> wrote:

> Would not

> Replace into A (<fieldlist>) select <key_fields>,<unchanged_fields>,
> <changed_fields> from A [cross] join B on( <key_fields>);

> do the trick? If a.rowid has an alias (i.e. integer primary key)
> then the modified rows would be deleted, but reinserted with their
> respective previous rowids. This may required switching foreign  
> keys off fort he duration of the update.

> -----Ursprüngliche Nachricht-----
> Von: [hidden email] [mailto:[hidden email]] Im Auftrag von skywind mailing lists
> Gesendet: Samstag, 04. Juni 2016 18:19
> An: SQLite mailing list <[hidden email]>
> Betreff: Re: [sqlite] UPDATE statement without FROM clause

> Hi,

> why? At the moment I have to run something like:

> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),... itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);

> Using a FROM clause I just need one scan through B (at least in principle). Now, I need N+1 scans.

> Regards,
> Hartwig

>> Am 2016-06-04 um 15:33 schrieb Gerry Snyder <[hidden email]>:
>>
>> If SQLite implemented the FROM it would just be a translation into the
>> complex and slow statements you want to avoid.
>>
>> Gerry Snyder
>> On Jun 4, 2016 9:19 AM, "skywind mailing lists"
>> <[hidden email]>
>> wrote:
>>
>>> Hi,
>>>
>>> I am using quite often SQL statements that update the data of one
>>> table with data from another table. This leads to some quite complex
>>> (and slow) statements because SQLite3 is not supporting a FROM clause
>>> in update statements. I am just wondering why the FROM clause is not
>>> supported by SQLite3?! Is this too complex to implement or is there
>>> simply no demand for these type of statements?
>>>
>>> Regards,
>>> Hartwig
>>>
>>>



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

Re: UPDATE statement without FROM clause

James K. Lowden
In reply to this post by skywind mailing lists
On Sat, 4 Jun 2016 18:18:36 +0200
skywind mailing lists <[hidden email]> wrote:

> At the moment I have to run something like:
>
> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),...
> itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
>
> Using a FROM clause I just need one scan through B (at least in
> principle). Now, I need N+1 scans.

Nonsense.  SQL provides no instruction to the implementation on how to
organize or traverse the data.  SQLite is free to scan B once, twice,
or not at all.  

Syntax has nothing to do with performance.  A correlated subquery is an
expression of logic; it's not meant to be taken literally, and often
isn't.  This particular form "just" needs to be recognized by the
optimizer.  

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

Re: UPDATE statement without FROM clause

skywind mailing lists
Hi,

of course there is in general a difference between syntax complexity and performance but unfortunately not in this case. And the „just“ is very often the most difficult part.

Regards,
Hartwig

> Am 2016-06-07 um 07:39 schrieb James K. Lowden <[hidden email]>:
>
> On Sat, 4 Jun 2016 18:18:36 +0200
> skywind mailing lists <[hidden email]> wrote:
>
>> At the moment I have to run something like:
>>
>> UPDATE A SET item1=(SELECT B.item FROM B WHERE B.ID=A.ID),...
>> itemN=... WHERE EXISTS (SELECT 1 FROM B WHERE B.ID=A.ID);
>>
>> Using a FROM clause I just need one scan through B (at least in
>> principle). Now, I need N+1 scans.
>
> Nonsense.  SQL provides no instruction to the implementation on how to
> organize or traverse the data.  SQLite is free to scan B once, twice,
> or not at all.  
>
> Syntax has nothing to do with performance.  A correlated subquery is an
> expression of logic; it's not meant to be taken literally, and often
> isn't.  This particular form "just" needs to be recognized by the
> optimizer.  
>
> --jkl
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

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