Tag Info

New answers tagged

1

You can simply do UPDATE my_tbl SET (a1, a2) = (a1 || 123465, a2 || 132456789) WHERE id = 321; To obtain a function, you simply have to replace the integer constants with the parameters. You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.


1

As per info linked in comment - it needs some preparing, cannot probably be used automatically: that is output from show engine innodb status You can see it if you do in commandline: begin; update employees set store_id = 0 where store_id = 1; and then in any (even the same) session show engine innodb status\G Where you look for section labeled ...


4

Order of table scan is never guaranteed. Order in which rows are locked is not guaranteed either. In addition, SQL Server has lock escalation, so you can't really say what the engine decides to lock, row, page, or table itself. Thus, deadlock may happen even if values of @id are different in concurrent sessions, but rows of interest happened to reside on ...


2

In addition to being slow, the statement's results will not be what you expect them to (which causes it to be slow): From the PostgreSQL documentation: from_list A list of table expressions, allowing columns from other tables to appear in the WHERE condition and the update expressions. This is similar to the list of tables that can be specified in the ...


0

Another approach: LOAD DATA into a new (temp) table UPDATE real JOIN new ON ... SET real.x=... WHERE ... -- Update any existing rows that need updating INSERT INTO new SELECT new LEFT JOIN real ON ... WHERE real.id IS NULL -- Add any rows that do not already exist


2

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example: INSERT INTO Contacts (number, ...


0

Can't comment yet, so posting as answer - you state in one comment that "not sure if there are ever duplicates within a given document anyway" - in that case (and in most other cases too) you should use INSERT ... ON DUPLICATE KEY UPDATE as it does not need to delete the existing row, so update overhead is only seen when the target row actually exists. ...


0

You can write a simple script externally or use the MySQL event scheduler. Both will work just fine. The benefit of the MySQL event scheduler is that it will be part of your database backups.


0

You may use REPLACE INTO. The disadvantage of it is that it creats high IO, as each existing record will be deleted and then inserted (as opposed to being updated). Try loading the new rows' IDs into a separate table on the destination server, then run a delete on the destination joining this new table with the existing table using the ID. After that you ...


1

Parse the JSON when the record in inserted, not later. (OK, maybe you need to collect the JSON in a "staging" table between arrival and processing.) When you parse the JSON, extract only the fields that you need to manipulate/search/etc in MySQL. Be conservative. Don't extract many fields. Leave the JSON as is in case you need the details later. It ...



Top 50 recent answers are included