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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Regards
John McMahon
j...@jspect.fastmail.com.au
04 2933 4203
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
