New answers tagged

1

In SQL, triggers can be fired "per row" or "per statement". SQLite only supports "per row" triggers (hence why your trigger fires once for each row in your INSERT statement), while SQL Server only supports "per statement" triggers (hence why your statement causes a single firing). Other engines (e.g. DB2 and PostgreSQL) support both sorts of trigger which ...


-1

You could create a UNIQUE index on (username).


0

INSERT INTO table_2 will always insert and never update. If you need different operation depends on which operation(insert or update) actually fired the trigger, you need to check it with IF UPDATING THEN ... END IF; or (IF INSERTING THEN ... END IF; )


0

Finding all nodes in a subtree to update them requires a recursive common table expression: -- update the root of all nodes in 'a's subtree to 'x' WITH RECURSIVE subtree(id) AS ( VALUES('a') UNION ALL SELECT Node.id FROM Node JOIN subtree ON Node.parent = subtree.id ) UPDATE Node SET root = 'x' WHERE id IN subtree; In SQLite, CTEs are ...


2

If you add all ColumnsNames (exclude the 'id' Column) after the Tablename, there will be Insert the serial automaticly like: INSERT INTO animals(nm,typ,tvi,tvf) select nm,typ,tvi,tvf from json_po..... You can also add a DEFAULT Value in your Column, to set a Default Value if the column is not in the Insert Column-list.


2

Query 1 Show 1 if the latest ID row in the database has scan date of today otherwise show 0. Can be written more simply (and more efficiently): SELECT CASE WHEN scan.date = CURDATE() THEN 1 ELSE 0 END AS datecheck FROM scan ORDER BY scan.id DESC LIMIT 1 ; and (if we like obfuscation): SELECT (scan.date = CURDATE()) AS datecheck FROM scan ORDER BY ...


4

Rephrasing your query it seems like you simply want to check if there's no row with today's date. It's a bit tricky to make it an conditional Insert: INSERT INTO scan (id, date) SELECT (MAX(id)+1), CURDATE() FROM scan HAVING COUNT(CASE WHEN scan.date = CURDATE() THEN 1 end) = 0; To make it work even with an empty table change to (COALESCE(MAX(id),0)+1)


2

Theoretically you could end up with duplicate usernames. In a scenario of high incoming requests, a race condition could occur. If you want to avoid this, just put an UNIQUE index on your column. You could create a UNIQUE index on username by using this query: CREATE UNIQUE INDEX IDX_USERNAME ON `user_tbl` (`username`); Prior to creating this unique ...



Top 50 recent answers are included