Tag Info

New answers tagged

0

Remember carefully the structure of the update statement in sql ;) Try this: UPDATE successlog INNER JOIN employeelist ON successlog.pid=employeelist.pId SET successlog.shiftid = employeelist.shiftid


1

I know it is not easy getting an execution plan for an update from MySQL since it only provides those on SELECT statements. But the clue might be in the order in which the records are updated, the evaluation of a WHERE which contains a IN with a large amount of static data, as well as the amount of connected reads and writes, intermediate caching, ...


0

Quick and dirty answer: UPDATE RBI.RBI_OV_F_DETAIL A SET (BRANCH_ID, BRANCH_NUMBER) = ( SELECT PRICE_BRANCH_ID, PRICE_BRANCH FROM RSE.RSE_SO_F_ORDERS B WHERE A.SOURCE_ID = B.FULL_ORDER_NUMBER AND A.METRIC_GROUP_ID='1' group by price_branch_id, price_branch); However if you're not expecting duplicates in the ...


0

Just a guess: Maybe mysql checks in the first query mysql checks 17 million rows, if one fit in the IN clause. in the second query you insert only 10000 rows, check the key by index and update the rows. If this is the case you could try to rewritee the first query to UPDATE... WHERE id <= 10000, but this only works if you realy have to update ...


2

UPDATE Table_1 SET EmailId = (SELECT d.EmailId FROM Table_2 d WHERE Table_1.Id = d.Id ) WHERE EXISTS (SELECT d.EmailId FROM Table_2 d WHERE Table_1.Id = d.Id AND Table_1.EmailId <> d.EmailId AND d.EmailId IS NOT NULL ...


1

Try to start a transaction or set autocommit=false prior the updates. start transaction; .... a lot of updates here ... commit; Also, the swapping may yield to the lack of physical RAM which occurs in some situations (big resources created by MySQL). You may also try to - increase the "key_buffer_size" to the maximum permitted by your SO and MySQL ...


0

It's hard to give you an exact answer without knowing what RDBMS you are using or what your table schema looks like. But here is an example for SQL Server. CREATE TRIGGER dbo.TR_MYTABLE_UPDATESTAMP ON dbo.[MYTABLE] AFTER INSERT,UPDATE AS BEGIN SET NOCOUNT ON; UPDATE I SET I.[MyTableUpdateDate] = GETDATE(), I.[MyTableUpdateBy] = SYSTEM_USER ...


2

I would probably do something like this: UPDATE tablename SET col1 = col1 + CASE WHEN col1 > $VAR THEN 2 ELSE 0 END, col2 = col2 + CASE WHEN col2 >= $VAR THEN 2 ELSE 0 END WHERE col1 > $VAR OR col2 >= $VAR Make sure that col1 and col2 are indexed if you are only updating a few rows and tablename has many rows.


1

Given that you can not afford to drop and recreate the table, this related answer would be a better fit: Best way to populate a new column in a large table? You might drop expendable indexes and recreate them when you are done (if they aren't completely expendable). And all the general advice for performance optimization applies. There is not much more ...


2

Per documentation: It is possible to access the two component numbers of a point as though the point were an array with indexes 0 and 1. But geometric types are not arrays. While the value as a whole can be NULL, parts of a point (or any geometric type) cannot. All geometric types can be input as string literals and cast to the respective type. ...


0

For now though since it's just a string you might be able to accomplish a simple change/deletion of a node with the regex_replace function. ..... For example, this is how I recently deleted a certain JSON node in a table (all rows): UPDATE my_db.my_table SET my_column = (regexp_replace(my_column::text, ',"some_json_node":(.*),', ','))::json WHERE NOT ...


0

Below this section it goes on to update, in the exact same manner, every table in the view. I cannot see why this is, as I have specified the Table_Year (which the table is partitioned on) within the query text. Shouldn't SQL only update the necessary table? The view meets all the partitioning requirements for both Table_Year and calStartDate. The ...


1

[Note: also answered at answers.SQLPerformance.com.] These aren't actually partitioned tables, and even if they were, partition elimination wouldn't really work for updating indexes unless all indexes were also partition-aligned. Since you are using Express Edition and can't actually use partitioning, I have a different approach to recommend: dynamic ...



Top 50 recent answers are included