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-
boun...@mailinglists.sqlite.org] 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
li...@jspect.fastmail.fm
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Regards
John McMahon
li...@jspect.fastmail.fm
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
