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