New answers tagged

3

This is taken from the MySQL documentation: Changes in MySQL 5.7.1 (2013-04-23, Milestone 11) ... If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. ...


1

Basically, you need a window function instead of the aggregation. Appending an OVER clause to an aggregate function makes it a window-aggregate function: SELECT 0, EXTRACT(EPOCH FROM ("time" - MIN("time") OVER ()))::bigint * 1000000, 1 FROM time_raw GROUP BY "time"; And since you want the minimum of the whole table, it's OVER () (nothing between the ...


3

You need to use JOIN by ID instead of the UNION. Using JOIN there is no need of the sub-query. Also ( before SELECT is not required after the INSERT INTO. So the working query is: INSERT INTO Company (Year, Company, Revenue, Profits) SELECT CI.Year, CI.Comp_Name AS Company, CF.Revenue, CF.Profits FROM Comp_Info AS CI JOIN Comp_Financials AS CF ON CF.ID = ...


0

A bit late, but maybe helpful nevertheless: The dreaded XE web interface (APEX) does not support running multiple statements or a complete script. You need to use a different tool (e.g. SQL Developer) or run one statement at a time.


0

The table is probably ENGINE=InnoDB. The default is probably to commit a transaction after every INSERT. You seem to be inserting one row at a time. Is the input file a list of INSERTs? Where did it come from? If it came from mysqldump, re run the dump with the option to do multi-row inserts. If the data is just text, arrange to use LOAD DATA. Can you ...


0

Assuming current Postgres version 9.5, since it has not been defined. Best solved with a LATERAL join (Postgres 9.3+). The minimal working form: INSERT INTO table_output SELECT f.* FROM table_input t, my_func(t.id) f; The same, more explicit: INSERT INTO table_output (foo, bar) SELECT f.foo, f.bar FROM table_input t CROSS JOIN LATERAL my_func(t.id) ...


2

You could use a trigger which insert into table2 what has been removed from table1. CREATE TABLE table1(id int, name varchar(10), level int); INSERT INTO table1(id, name, level) VALUES (0, 'a', 0) , (1, 'b', 1) , (2, 'c', 0) , (3, 'd', 2) , (4, 'e', 1) , (4, 'f', 0); CREATE TABLE table2(id int, name varchar(10), level int); DELIMITER // ...


1

Insert everything into a temp table first. Then your insert and delete are simply everything from the temp table rather than your complicated query. This solves your problem of how to do both halves and is probably going to perform better than running your complicated query multiple times. I do more MS-SQL than My-SQL so I'll link to another answer on Stack ...


3

What you want to do is called a Cartesian product and uses CROSS JOIN: SELECT NULL, t1.UID, t2.UID FROM table1 t1 CROSS JOIN table2 t2 WHERE T1.`Groups` LIKE 'V' AND t2. `IsDefault` = 1; This mixes each rows from table1 with each rows from table2. (see sqlfiddle)


1

It's better to write such preventive measures in Application layer, database layer may give you short term benefits. But it's Application layer which can cater your all demands of new logic in future.


2

Since batch inserts and LOAD DATA are much more efficient than lots of single-row inserts, your request is backward. Rethink the real goal, and how to express it.


0

Once someone executes INSERT DELAYED, a system thread will open in order to track and mitigate INSERTs of this kind. MySQL 5.6 deprecates it and 5.7 does not support it. What is more, in terms of MySQL Replication, Slaves will completely ignore an INSERT DELAYED command from a Master and execute them as a standard INSERT. Please read the MySQL 5.5 ...


1

Be aware - I posted this before index definitions were posted Insert in the order of the PK to keep fragmentation down INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT 2, value, 0 FROM Z_SIMULATION_0 WHERE ID >= 1 AND ID <= 500000 ORDER BY value; or INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT top (...


4

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on. When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or ...


1

I cross-posted this to Stackoverflow and found a working answer, even if it meant having to type out the column names (which explains why my WHERE clause didn't work) SELECT ins.[name], ins.[created_at] FROM INSERTED ins JOIN DELETED del ON ins.id = del.id WHERE del.[name] <> ins.[name] OR del.[age] <> ins.[age]; ...


1

innodb_log_buffer_size should not be set to 4G. The default of 8M is usually sufficient; let's compromise on 100M, ok? case when exists ( select ... ) then ( select ... ) else 0 end --> coalesce( ( select ... ), 0 ) "Creation time" comes out either as YMD format or DMY; did you want that inconsistency? Indexes needed: INDEX(classDesc, LookupId, ...


2

ALTER TRIGGER [dbo].[t_upd_insert] ON [dbo].[Player] FOR INSERT, UPDATE AS SET XACT_ABORT, NOCOUNT ON; BEGIN IF (update(name)) BEGIN INSERT INTO dbo.Changes ( [name] [created_at] ) SELECT ins.[name], ins.[created_at] FROM INSERTED ins END


0

If you are inserting through loop. Then you can have counter and set it for max rows to insert. If it exceeds then you can leave the loop and set an error message. Or if you have auto increment id then you can take max id from table, and run till the id exceeds your limit. You can get every inserted id by LAST_INSERT_ID()



Top 50 recent answers are included