SQLite Update With CTE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

SQLite Update With CTE

John McMahon-2
Hi

I am rewriting an old Perl script that selectively updates data from one
table to another using this statement:

UPDATE CUSTOMERS
SET
     cust1    = ?,
     cust2    = NULL,
     street   = ?,
     town     = ?,
     postcode = ?
  WHERE custnum = ?

I am intending to replace it with something like this where 'test' is
the CTE:

UPDATE CUSTOMERS as c
SET
     cust1    = (select customer from test where custnum = c.custnum),
     cust2    = NULL,
     street   = (select address  from test where custnum = c.custnum),
     town     = (select town     from test where custnum = c.custnum),
     postcode = (select postcode from test where custnum = c.custnum)
  WHERE custnum = (select custnum from test where custnum = c.custnum)

My question is, do I need this part of the statement:
  WHERE custnum = (select custnum from test where custnum = c.custnum)

when I have the other 'where custnum = c.custnum' clauses.

I came across some Web examples that suggest that I might not. I haven't
tested yet and am a little unsure.

Any guidance would be appreciated.

John

--
Regards
    John McMahon
       [hidden email]


_______________________________________________
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: SQLite Update With CTE

Clemens Ladisch
John McMahon wrote:

> UPDATE CUSTOMERS as c
> SET
>     cust1    = (select customer from test where custnum = c.custnum),
>     cust2    = NULL,
>     street   = (select address  from test where custnum = c.custnum),
>     town     = (select town     from test where custnum = c.custnum),
>     postcode = (select postcode from test where custnum = c.custnum)
>  WHERE custnum = (select custnum from test where custnum = c.custnum)
>
> My question is, do I need this part of the statement:
>  WHERE custnum = (select custnum from test where custnum = c.custnum)
>
> when I have the other 'where custnum = c.custnum' clauses.

The WHERE clause on the UPDATE itself filters the rows that will be
updated.

If you know that "test" contains new values for all customers, you do
not need the WHERE. But if you (might) update only a subset of
customers, you need it.


And that last subquery is not used for assignment, so writing it in
a different form might be clearer:
  WHERE EXISTS (SELECT * FROM test WHERE custnum = c.custnum)
or
  WHERE custnum IN (SELECT custnum FROM test)


And UPDATE does not support AS.


Regards,
Clemens
_______________________________________________
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: SQLite Update With CTE

John McMahon

On 22/08/2017 16:41, Clemens Ladisch wrote:

> John McMahon wrote:
>> UPDATE CUSTOMERS as c
>> SET
>>      cust1    = (select customer from test where custnum = c.custnum),
>>      cust2    = NULL,
>>      street   = (select address  from test where custnum = c.custnum),
>>      town     = (select town     from test where custnum = c.custnum),
>>      postcode = (select postcode from test where custnum = c.custnum)
>>   WHERE custnum = (select custnum from test where custnum = c.custnum)
>>
>> My question is, do I need this part of the statement:
>>   WHERE custnum = (select custnum from test where custnum = c.custnum)
>>
>> when I have the other 'where custnum = c.custnum' clauses.
>
> The WHERE clause on the UPDATE itself filters the rows that will be
> updated.
>
> If you know that "test" contains new values for all customers, you do
> not need the WHERE. But if you (might) update only a subset of
> customers, you need it.

it doesn't

>
>
> And that last subquery is not used for assignment, so writing it in
> a different form might be clearer:
>    WHERE EXISTS (SELECT * FROM test WHERE custnum = c.custnum)
> or
>    WHERE custnum IN (SELECT custnum FROM test)
>
>
> And UPDATE does not support AS.

So this
UPDATE CUSTOMERS as c
SET
     cust1    = (select customer from test where custnum = c.custnum),
     cust2    = NULL,
        ...
should be
UPDATE CUSTOMERS -- remove 'as c'
SET
     cust1    = (select customer from test where custnum = c.custnum),
change to
     cust1    = (select customer from test where custnum =
CUSTOMERS.custnum), -- excuse the line wrap

or perhaps
     cust1    = (select customer from test as t where t.custnum = custnum),

     cust2    = NULL,
        ...

and end with
  WHERE custnum IN (SELECT custnum FROM test)

Thank you,
John

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

--
Regards
    John McMahon
         [hidden email]
         04 2933 4203

_______________________________________________
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: SQLite Update With CTE

Clemens Ladisch
John McMahon wrote:
> should be
> UPDATE CUSTOMERS -- remove 'as c'
> SET
>     cust1    = (select customer from test where custnum = CUSTOMERS.custnum),
>  WHERE custnum IN (SELECT custnum FROM test)

Yes.


Regards,
Clemens
_______________________________________________
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: SQLite Update With CTE

Keith Medcalf
In reply to this post by John McMahon-2

You could also -- if using a version of SQLite3 that supports row values (3.15.0 and later) -- do something like this:

UPDATE CUSTOMERS
   SET (cust1, cust2, street, town, postcode) = (SELECT customer, NULL, address, town, postcode
                                                   FROM test
                                                  WHERE custnum = customers.custnum)
 WHERE custnum in (select custnum from test);

It will get all the updates in a single correlated subquery rather than four ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of John McMahon
>Sent: Monday, 21 August, 2017 22:25
>To: SQLite Users
>Subject: [sqlite] SQLite Update With CTE
>
>Hi
>
>I am rewriting an old Perl script that selectively updates data from
>one
>table to another using this statement:
>
>UPDATE CUSTOMERS
>SET
>     cust1    = ?,
>     cust2    = NULL,
>     street   = ?,
>     town     = ?,
>     postcode = ?
>  WHERE custnum = ?
>
>I am intending to replace it with something like this where 'test' is
>the CTE:
>
>UPDATE CUSTOMERS as c
>SET
>     cust1    = (select customer from test where custnum =
>c.custnum),
>     cust2    = NULL,
>     street   = (select address  from test where custnum =
>c.custnum),
>     town     = (select town     from test where custnum =
>c.custnum),
>     postcode = (select postcode from test where custnum = c.custnum)
>  WHERE custnum = (select custnum from test where custnum =
>c.custnum)
>
>My question is, do I need this part of the statement:
>  WHERE custnum = (select custnum from test where custnum =
>c.custnum)
>
>when I have the other 'where custnum = c.custnum' clauses.
>
>I came across some Web examples that suggest that I might not. I
>haven't
>tested yet and am a little unsure.
>
>Any guidance would be appreciated.
>
>John
>
>--
>Regards
>    John McMahon
>       [hidden email]
>
>
>_______________________________________________
>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: SQLite Update With CTE

John McMahon-2
Thanks Keith


On 23/08/2017 00:06, Keith Medcalf wrote:
>
> You could also -- if using a version of SQLite3 that supports row values (3.15.0 and later) -- do something like this:
SQLite version 3.15.1 2016-11-04 12:08:49, I usually update near the end
of year unless I see something particularly interesting, eg. CTEs when
they were introduced.

>
> UPDATE CUSTOMERS
>     SET (cust1, cust2, street, town, postcode) = (SELECT customer, NULL, address, town, postcode
>                                                     FROM test
>                                                    WHERE custnum = customers.custnum)
>   WHERE custnum in (select custnum from test);
>
> It will get all the updates in a single correlated subquery rather than four ...
Thank you, I just compared the drawings in "lang_update.html" for
versions 3.10 and 3.15, I had missed that and it is functionality I was
wishing for.

>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.
>
>> -----Original Message-----
>> From: sqlite-users [mailto:sqlite-users-
>> [hidden email]] On Behalf Of John McMahon
>> Sent: Monday, 21 August, 2017 22:25
>> To: SQLite Users
>> Subject: [sqlite] SQLite Update With CTE
>>
>> Hi
>>
>> I am rewriting an old Perl script that selectively updates data from
>> one
>> table to another using this statement:
>>
>> UPDATE CUSTOMERS
>> SET
>>      cust1    = ?,
>>      cust2    = NULL,
>>      street   = ?,
>>      town     = ?,
>>      postcode = ?
>>   WHERE custnum = ?
>>
>> I am intending to replace it with something like this where 'test' is
>> the CTE:
>>
>> UPDATE CUSTOMERS as c
>> SET
>>      cust1    = (select customer from test where custnum =
>> c.custnum),
>>      cust2    = NULL,
>>      street   = (select address  from test where custnum =
>> c.custnum),
>>      town     = (select town     from test where custnum =
>> c.custnum),
>>      postcode = (select postcode from test where custnum = c.custnum)
>>   WHERE custnum = (select custnum from test where custnum =
>> c.custnum)
>>
>> My question is, do I need this part of the statement:
>>   WHERE custnum = (select custnum from test where custnum =
>> c.custnum)
>>
>> when I have the other 'where custnum = c.custnum' clauses.
>>
>> I came across some Web examples that suggest that I might not. I
>> haven't
>> tested yet and am a little unsure.
>>
>> Any guidance would be appreciated.
>>
>> John
>>
>> --
>> Regards
>>     John McMahon
>>        [hidden email]
>>
>>
>> _______________________________________________
>> 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
>

--
Regards
    John McMahon
       [hidden email]


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