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-
>[email protected]] 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
> [email protected]
>
>
>_______________________________________________
>sqlite-users mailing list
>[email protected]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
