I agree. SQL is quite deficient in terms of set-oriented updates. INSERT is more or less UNION, but UPDATE and DELETE have no set-oriented forms.
The relational algebra describes operations on sets of tuples, where the only operation on attributes is to compare them by name or equal value. SQL implements most of the relational algebra directly, and all of it by combining operations. Updates should be semantically equivalent to an operation from the Relational Algebra followed by assignment (new value replaces old). INSERT works like that, but UPDATE and DELETE do not. Try writing a query to update all the salaries for a company where all the new salaries are found in some other table (ie a JOIN). Try writing a query to delete all the employees listed in some other table (another JOIN). Andl already has set-oriented UPDATE and DELETE, as well as the familiar predicate and computed styles. Thank you for asking. As it happens I have not been able to fully implement them on SQLite so far, because of limitations in the underlying SQL. Andl cannot perform any alterations on the columns of known tables, because that would change its relational type. It's easy to create a new table and copy data, but the issue of the proper way to handle versioning and migrations is still open. Regards David M Bennett FACS Andl - A New Database Language - andl.org -----Original Message----- From: sqlite-users-boun...@mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Simon Slavin Sent: Wednesday, 17 June 2015 6:11 AM To: sqlite-users at sqlite.org; General Discussion of SQLite Database Subject: Re: [sqlite] Mozilla wiki 'avoid SQLite' On 16 Jun 2015, at 7:44pm, James K. Lowden <jklowden at schemamania.org> wrote: > <david at andl.org> wrote: > >> What would make a database programming better, or best? > > Two things I've often pointed to are namespaces and regular > expressions. Another is compound datatypes. I don't have your problem with namespaces since, to me, they really are just prefixes. I do agree that regular expressions are a problem. They don't really belong in the language but they are very convenient when they are there. SQLite has JOINs (or sub-selects, which amount to the same thing at a low level) for INSERT and for SELECT but not for UPDATE. A few times when working with SQLite I've found myself writing UPDATE ... JOIN. And then having to do the job in my own code instead. And if you add JOIN to UPDATE you should probably add it to DELETE FROM too. The other problem with SQLite is the lack of ALTER TABLE ... DROP COLUMN. But to support it you need SQLite to have a proper internal model of which columns are used for what, rather than to just store and reparse the CREATE TABLE commands. The thing I always found interesting about SQL was that it picks three English words, INSERT, DELETE, UPDATE, and says that that's all you need to do. And it's right ! Is there something special about the 'three-ness' of database operations ? Or are you meant to think of it as two writing operations (INSERT, DELETE) and a convenience operation which combines them (UPDATE) ? If there was another word, what would it be ? REPLACE ? DUPLICATE ? Also, why is there only one English word needed for reading operations ? What would a database language look like if it has more than one word ? Would there be a difference between FIND and SCAN ? Simon. _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
