Ciro Santilli - SQLite with
rowid: stackoverflow.com/questions/8190541/deleting-duplicate-rows-from-sqlite-database - SQL Server has crazy "CTEs" change backing table extension: stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server
UPSERT is extremely handy, and reduces the number of find, check on server, update loops. But RETURNING is a fundamental part of that (to get the updated/existing) ID. Can't believe SQL hasn't standardized it yet as of 2022. But both SQLite and Postgres support it with similar syntax thankfully.Attempt at nodejs/sequelize/raw/upsert.js:
- stackoverflow.com/questions/48816629/on-conflict-do-nothing-in-postgres-with-a-not-null-constraint OP unable to provide a minimal exampe, but it is likely the problem
- dba.stackexchange.com/questions/292428/postgresql-upsert-issue-with-not-null-columns
Related on more complex constraints:
It is mind blowing that this is not possible... the only way to avoid ambiguity in JOINs with column name conflicts is to give aliases to each column...
An example where
SELECT FOR UPDATE is a good solution to an use case can be seen at: nodejs/sequelize/raw/parallel_select_and_update.js.SELECT FOR UPDATE vs/together with the SQL transaction isolation level is commented at: stackoverflow.com/questions/10935850/when-to-use-select-for-update.SQL's implementation of database triggers.
This feature is really cool, as it allows you to keep caches up to date!
In particular, everything that happens in a trigger happens as if it were in a transaction. This way, you can do less explicit transactions when you use triggers. It is a bit like the advantages of SQL CASCADE.
DBMS:
TODO what is the standard compliant syntax?
PostgreSQL requires you to define a SQL stored procedure: stackoverflow.com/questions/28149494/is-it-possible-to-create-trigger-without-execute-procedure-in-postgresql Their syntax may be standard compliant, not sure about the
EXECUTE part. Their docs: www.postgresql.org/docs/current/sql-createtrigger.htmlSQLite does not support SQL stored procedures at all, so maybe that's why they can't be standard compliant here: stackoverflow.com/questions/3335162/creating-stored-procedure-in-sqlite
SQL:1999 11.38 covers "Trigger definition". The Abstract syntax tree starts with the
CREATE TRIGGER and ends in:
<triggered SQL statement> ::=
<SQL procedure statement>This is defined at 13.5 "SQL procedure statement", but that is humongous and I'm not sure what it is at all.
In this example we cache track the number of posts per user on a cache column.
Basic example tested on SQLite 3.40.1, Ubuntu 23.04:
output:
sqlite3 :memory: 'select 1 union select 2'1
2Two columns two rows:
output:
sqlite3 :memory: <<EOF
select * from (values (1, 2), (2, 3))
union
select * from (values (2, 3), (3, 4))
EOF1|2
2|3
3|4Note how duplicates are removed, to keep them we
output:
UNION ALL instead:
sqlite3 :memory: <<EOF
select * from (values (1, 2), (2, 3))
union all
select * from (values (2, 3), (3, 4))
EOF1|2
2|3
2|3
3|4This happens when you calculate a bunch of values on your program, and then want to save the to SQL.
Bibliography:
Dumping examples under nodejs/sequelize/raw/many_to_many.js.
Not possible without subqueries in the standard syntax, a huge shame: stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql-server
Demo under: nodejs/sequelize/raw/many_to_many.js.
NO way in the SQL standard apparently, but you'd hope that implementation status would be similar to UPDATE with JOIN, but not even!
- PostgreSQL: possible with
DELETE FROM USING: stackoverflow.com/questions/11753904/postgresql-delete-with-inner-join - SQLite: not possible without subqueries as of 3.35 far: stackoverflow.com/questions/24511153/how-delete-table-inner-join-with-other-table-in-sqlite, Does not appear to have any relevant features at: www.sqlite.org/lang_delete.html
ORM
- Sequelize: no support of course: stackoverflow.com/questions/40890131/sequelize-destroy-record-with-join