We’re rewarding the question askers & reputations are being recalculated! Read more.

Questions tagged [update]

UPDATE changes the values of the specified columns in all rows that satisfy the condition. Only the columns to be modified need be mentioned in the SET clause; columns not explicitly modified retain their previous values.

117 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
3
votes
0answers
493 views

How to optimize update using join in postgresql

I'm using PostgreSQL 9.3.5 on a CentOS system. I have a very large table (twenty million rows, 50+ column) that aggregates data from a few hundred systems. Several times a day each of the systems ...
3
votes
0answers
305 views

Unable to Update on SQL Server 2008 R2

This is a bit strange so bear with me please. After installing a new SQL Server 2008 R2 on a new server (Windows 2012 Standard) just like the production one, I tried to connect my web aplication (...
2
votes
0answers
123 views

SQL Server 2016 SP2 CU9 - WFC Failover failing

I have updated my SQL Server 2016 on one of my Windows Failover Cluster nodes successfully. When I attempted to failover one of my SQL instances onto the newly patched node, SQL Server service fails ...
2
votes
0answers
21 views

Which table was updated by the last query?

When I perform an update query, MySQL server gives me information about how many rows were updated. But I wish to know in which table they were updated. Is that information somewhere available? The ...
2
votes
0answers
32 views

Need to update a table with an iterative number based on a subquery

Massive edit to follow procedure and enable reproducing this in a fiddler somewhere. Table 1, schedule: CREATE TABLE `schedule` ( `scheduleid` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY, `...
2
votes
0answers
1k views

Postgres bulk upsert performance

I am doing bulk UPSERT on a table with 30 million rows. The table has just two columns (varchar as a primary key and integer). The input data is imported to the temporary table first, and then bulk ...
2
votes
0answers
110 views

Stored procedure to UPDATE row to “closest” match in one table from data in another table

I have a table called ThawLaneA that has a populated column ThawTime that has DATETIME value. The first row starts at a specific time, and each row from there "increases" by a specific amount of time (...
2
votes
1answer
47 views

find 16,000 words and replace with ##

I need to do a find a replace but rather than find one word to replace with another I have about 16k words, so as an example; table 1 has two words apple,orange - table 2 has the contents of a book, i ...
2
votes
1answer
50 views

Regularly updating table with huge amount of rows

I have a table containing (in addition to some metadata) a score and a boolean flag: CREATE TABLE scores ( score_id SERIAL NOT NULL CONSTRAINT score_pkey PRIMARY KEY, user_id ...
2
votes
0answers
427 views

Bulk Updates (inserts, update) and check constraints

Every now and then I need to update all the records (of one attribute) in a very large table. My default strategy is to create a new table, fill all the new values, create indexes, and rename the ...
2
votes
1answer
2k views

How to update primary key table based on foreign key with ON DUPLICATE KEY UPDATE

I have a users table with autoincrement id as primary key, and unique key on email. The email and name fields are from facebook. +----+-------+------+ | id | email | name | +----+-------+------+ And ...
2
votes
0answers
627 views

Postgres update query complaining about text value when only NULL values are passed

I am using Postgres 9.4.6. Is there any reason why the below update query would execute successfully here:- UPDATE "Predictions" SET "userid" = c."userid", "Fixture_No" = c."Fixture_No",...
2
votes
1answer
110 views

How to avoid row locks on both tables joined in an update?

I have little knowledge about MySQL internals, but I'm experiencing that in a query like: UPDATE t1 JOIN t2 ON t1.id = t2.id SET t1.a = x, t1.b = y, [, ...] WHERE [ ... ] rows from both t1 and ...
2
votes
0answers
442 views

Manual postgres migration for Django (python) app

I have a Django (python) data model called Message: class Message(models.Model): text = models.CharField(max_length=50) sending_time = models.DateTimeField(auto_now_add=True) I added sender =...
2
votes
1answer
74 views

UPDATE on column referenced in sub-query

We have a query that updates a column for all rows matching a condition which involves a subselect on said column, like the following: update CE_WORKSET_READ_ENTRY workset set workSet.MARK = 0, ...
2
votes
0answers
13k views

MySQL - Truncated incorrect datetime value on update statement

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day). I need to ...
2
votes
1answer
73 views

Overwrite event - data from SQL Server to MySQL

In MySQL is it possible to execute an update query with values selected from external SQL Server database? I'd like to execute every night a timed query, which overwrites some data in MySQL with data ...
2
votes
0answers
882 views

innodb update with limit locks all matching (ignores limit)

For a query similar to this: UPDATE myinnotable1 SET value=5,updated=now(),status=1 WHERE status>10 AND message="match msg" LIMIT 1; I found that innodb engine locks ALL rows matching criteria ...
2
votes
1answer
73 views

Translate SQL Server update statement to MySQL

I have a development environment with MySQL and a production environment with SQL Server. This update statement works with SQL Server: UPDATE THIS SET <list_of_column_assignments> FROM <...
2
votes
0answers
549 views

Best Practice to update columnstore indexed tables

I have a table with aprox. 22 Bn rows, for each month I have made one partition that and each partition has two data files. I created the columnstore index on this table and the performance ...
2
votes
1answer
681 views

Very slow update with subquery (different tables)

I run this query to update "domains" table based on "log" table: UPDATE domains SET domains.mx_collected = 0 WHERE domains.domain IN (SELECT log.mail as domain_name FROM log WHERE `time` >= ...
1
vote
0answers
57 views

Postgres 10.6: UPDATE on partially indexed column sometimes hangs

I have a table (6 small columns 2M+ rows) with a column dead. There are two indexes on this table, both are partial with dead = false. From time to time I want to scan the table and mark rows with ...
1
vote
1answer
180 views

Correcting and Detecting overlapped dates with CTE

I have a CTE that marks (as bits) overlapped records based on certain criteria. After filtering, I need to update these records to "correct" them and make them sequential, but I'm getting a 4104 on ...
1
vote
1answer
62 views

Why is this very simple UPDATE so much slower than the SELECT it contains?

In MariaDB 10.3.9: SELECT COUNT(*) FROM photos vs UPDATE counts SET countValue = (SELECT COUNT(*) FROM photos) WHERE countName = 'photos'; The select takes 1.1s vs the update which takes 2.6s. ...
1
vote
0answers
37 views

Optimization Technique For Update Query in MySQL 5.1.73-log

I am trying to optimize an update query on InnoDB table in MySQL 5.1.73-log. As per my understanding we can not use EXPLAIN on update query for 5.1 version. So what will be the alternate course of ...
1
vote
1answer
13 views

Return an error where the ID matches, but the owner_id doesn't

I've seen a common pattern like this used to update a record: UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234 However, what if there is a requirement that a non-existentent ID is ...
1
vote
0answers
89 views

Database Behavior while executing UPDATE and INSERTs on the same table

My gear: Pentaho BI Server 8.0 AS400 V7 My scenario: I have two processes that sometimes run at same time in the same table. This table has about 600M rows, so, an update can take long time to ...
1
vote
2answers
202 views

MySQL - Deduct from two columns with a remainder

I have the following table: Id, balance, otherBalance ============================= 1, 100, 50 I would like to create an update query that receives a parameter 'amount' and does the ...
1
vote
0answers
332 views

Update Quantity Available using Quantity from another table

You will demonstrate an update that includes two tables and uses a subquery. Make sure to prove that your update executed correctly by showing data in the tables before and after the update. ...
1
vote
0answers
107 views

conditional UPDATE race conditions

In a conditional UPDATE like this: UPDATE MY_TABLE SET my_coulmn = "my value" -- many other updates WHERE ... -- many conditions Do I have any guarantee that I will only update those records ...
1
vote
1answer
25 views

I have 10 tables in schema icmdb2admin each table has one column named pdfname i need to update the values in the pdfname column with L1.pdfname.V1

DECLARE i record; BEGIN FOR i IN SELECT 'icmdb2admin.' || tablename as tbp FROM pg_tables WHERE schemaname = 'icmdb2admin' and tablename <> '...
1
vote
2answers
181 views

Replacing MYSQL table with data from another table - no downtime

I have two tables with identical structures: Table1 and Table2 id INT PRIMARY KEY, a TINYINT, b TINYINT, c TINYINT Table1 is actively being used by queries from other sessions; Table2 is not ...
1
vote
0answers
41 views

An SQL update query works fine unless it is invoked by an SSRS subscription. Why?

A stored procedure that updates data in production data tables was written by a very talented staff member (with developer-only rights) and works fine when executed in SSMS. However, the same SP does ...
1
vote
0answers
789 views

Updating varchar to longtext leads to data loss

I run a number of MySQL servers for my Java application. I have a table that looks like this: +---------------+----------------+------+-----+---------+-------+ | Field | Type | ...
1
vote
0answers
180 views

why an update doesn't update a row if the where clause exists?

I'm working with SQL Server 2012 SP2 and I have a problem with a stored procedure. I have a table where we do a lot of things in a second: we insert and update rows very often. I have a stored ...
1
vote
0answers
2k views

PostgreSQL Partitioned table update trigger

I have a date-partitioned table (inheritance partitioninng just like the one pg_partman does) which is referenced by quite a few tables in my database. To resolve the FKs checking for referenced field ...
1
vote
0answers
531 views

MySQL Update column with case on joined tables

I have 2 tables and I want to update table 1 using a case check on table 2: update cms.Clients c #c_id unique join cms.ClientDomains d #c_id not unique on c.c_id = d.c_id set c.country = case ...
1
vote
1answer
36 views

How do I uniquely update values of a text column before applying UNIQUE constraint?

I need to apply a unique constraint to a TEXT field which may or may not already contain duplicate values. If for instance I have ABC twice, I don't mind renaming them as ABC-1, ABC-2, but what if I ...
1
vote
0answers
614 views

Update row value with a sub-select join

I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table. The query looks like this (...
1
vote
0answers
413 views

PostgreSQL 9.3: Minimize locking when updating a large table with primary key

I am building a DB solution to handle a fast stream of online events. Each event UPSERTs a row in a location_update table, using its car_id primary key. Since there are very few new car_ids, the ...
1
vote
0answers
76 views

How to use the value from record like as varchar?

I'm trying update a old table with result of selec for another tables but get a error using this sintaxe (e.g.): DO $$ DECLARE _roles refcursor; _onerole varchar; _record RECORD; ...
1
vote
1answer
69 views

Index that mimics full-text-index

I have a database that is retrieving information quite slowly, I asked a question and was told to use full-text-indexing, this worked, but now we switched over to a .sdf file and Sql Server Compact ...
1
vote
0answers
270 views

Update on a small table times out

We use Microsoft SQL Server Express Edition with Advanced Services 2008 R2 SP2 We have two locations. Each location has one sql server that serves about 20 client computers. Client computers are ...
1
vote
0answers
1k views

Updating +150,000 rows using .sql file causes ERROR 2013 Lost connection to MySQL server during query

I am using ubuntu 12.04. I have a .sql file that is around 5 megabytes. When I use the command: mysql -u -p -v database query.sql The server prints out the content of the file (because of --verbose) ...
1
vote
1answer
264 views

Partial updates with functions

My applications rely on PostgreSQL functions to carry out operations on the database. I'm having trouble figuring out a good practice to make partial updates where only some fields are provided. Let'...
1
vote
1answer
2k views

MySQL Cluster error on UPDATE: increase MaxNoOfConcurrentOperations

I had a table with ~0.5M rows and tried to execute a simple query, something like: UPDATE table SET col1=-90 + RAND()*90, col2=-180+RAND()*360 WHERE col1=9999 but the result was ERROR 1297 (HY000)...
1
vote
1answer
504 views

scenario to update or insert big amount of data

I try to find the best scenario to update or insert 1 million contacts in my postgres database. My actual scenario is: read contacts 1000 per 1000 and try to insert multiple rows with one request if ...
0
votes
1answer
19 views

Update a record whilst creating a link to a parent record

Someones account was deleted from a system we have. They created a new account for her and moved on. But it turns out she wasnt deleted from SQL - when they delete in the app, the record is marked as '...
0
votes
0answers
22 views

Fill in Ending Balance from previous months balance

I need to fill in the Ending Balance of each Year / Period where it is 0 (zero) but a previous period for that year and company has an ending balance - basically carrying forward the ending balance ...
0
votes
1answer
18 views

how mongo db does locking for updates/inserts?

I have many data for each user that with write heavy and many for each separate users that there is not any relation between user's data; so there is two options for me to store them: 1. keep all user'...