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.

Filter by
Sorted by
Tagged with
0
votes
2answers
40 views

Why does my table keep getting corrupted after removing large amounts of data?

I have a database that is about 400 gigs. The majority of the space is consumed by images of utility bills stored in string form as varchar(max) in a single table. It's a poor database design that I ...
0
votes
2answers
76 views

Slow update performance

There is Postgres 10 (10.5 (Ubuntu 10.5-1.pgdg16.04+1)). The server has 256 GB RAM. shared_buffers = 64GB effective_cache_size = 192GB maintenance_work_mem = 2GB checkpoint_completion_target = 0.9 ...
-1
votes
1answer
39 views

How can I manage live database updates & migrations for a team? [closed]

I don't want to directly access the database and run SQL UPDATE commands. It's error prone and there's no way to trace previous changes, including who did it. Is there an open source tool i can use to ...
0
votes
1answer
15 views

What is the effect of number of CPU cores on update operations in Postgres?

I have batch update operations being sent to the PostgreSQL server to be executed. Each operation (batch) will be updating rows in the same table but it is assured that they will be updating different ...
0
votes
1answer
203 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'...
0
votes
1answer
14 views

PostgreSQL update table then insert on another table

I have a User table and a Log table. I need to update a field on the User table and insert a new entry on the Log table recording the update. I have written the statement for the update: UPDATE ...
0
votes
1answer
34 views

What is a good strategy for updating lot of rows in postgersql?

I have an inventory table which contains item_id and the quantity remaining of the item (And also some other meta data). I have an administrator update the inventory by uploading a CSV file which ...
1
vote
1answer
35 views

update most recent rows only?

I have a database of a garage of rented cars with a table called Cars with the following columns : Type,First_seen_in_garage,Last_seen_in_garage The table initially have the following rows : Ferrari,...
0
votes
1answer
38 views

Should an update statement query plan ever go parallel?

If an update statement's query plan does go parallel, does it guarantee a communication buffer resource deadlock, or is that determined by whether the parallel plan is using all cores?
0
votes
1answer
460 views

Trigger on 2 tables : issue with INSERT and UPDATE

This is related to my previous question: Trigger to cascade updates to a second table. In my Postgres 9.6 DB, I had 2 triggers on emprise (formerly table1), supposed to insert or update some fields ...
2
votes
1answer
3k 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 ...
0
votes
1answer
19 views

Simple Update Join much slower than it should be (MYSQL)

This is a simple Update Join that updates only about 100 rows: Update A INNER JOIN B using(id) SET A.active = 1 WHERE A.date > '2020' This takes about 30 seconds to run, despite the fact that: ...
17
votes
4answers
38k views

Effect of an index on update statements where update column is not in an index

I constantly see people say that indexes slow down update, delete and insert. This is used as a blanket statement, as if it is an absolute. While tuning my database to improve performance, I keep ...
0
votes
2answers
97 views

Why is a trigger preventing update? [closed]

I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week ...
0
votes
1answer
23 views

updating n number of records mysql database

I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key update producedItem pi inner ...
0
votes
2answers
14 views

Update postgres json value by other attribute

I'm using PostgreSQL 9.6 and I got the following table: Cars ID Name Data 1 abc {"color":"red","wheels":4,"my-value":false} ...
0
votes
3answers
84 views

How to prevent trigger from overwriting values that were explicitly set?

I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?): create temp table ...
0
votes
2answers
54 views

How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?

I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys ...
8
votes
2answers
356 views

UPDATE statement behavior

I have a question regarding inner workings of UPDATE statements with regards to SQL Server. I am trying to understand what will happen if the following 2 update statements are received or serviced at ...
-2
votes
2answers
160 views

Are lost updates completely handled in SQL Server or is manual intervention still required?

As lost updates occur when 2 different transaction try to update the same thing at the same time - different sessions update the same row; the last one overwrites the changes made by the first. We ...
0
votes
0answers
48 views

Making a trigger to update FK based on columns in another table and an external table

I'm inserting data into a database and I want the FKs to be populated automatically based on other information that links each FK to its corresponding PK. Here's my plan: Create an external table of ...
0
votes
1answer
111 views

Query tuning update with hash flow distinct operator

Need some help in understanding the slowness in one of the UPDATE statements like below:- UPDATE TOP (100) xyz SET xyz.flag = 1 OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc....
0
votes
1answer
31 views

INSERT .. ON CONFLICT DO UPDATE SET error

When I use the following query : cur.execute("""INSERT INTO TABLE (SELECT ID_tmp,First_seen_tmp,Last_seen_tmp,Duration_tmp FROM tmp_table) ON CONFLICT (ID) DO UPDATE SET Last_seen=...
0
votes
1answer
32 views

Why is ALTER faster than UPDATE (for full table rewrites)?

Let's create a garbage table.. CREATE TABLE foo AS SELECT x::text FROM generate_series(1,20e6) AS t(x); Time: 14077.654 ms (00:14.078) Now, let's look at boring UPDATE, UPDATE foo SET x = 'foo'; ...
0
votes
1answer
34 views

SQL Server :: how to move country code column to another table?

I have 2 tables: tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG). Like this: CountryName TwoCharCountryCode ...
0
votes
0answers
25 views

insert or update with summary subquery

i have a problem with follwing query INSERT IGNORE INTO cartellino_anno (iddipendente, mese, ordinarie,effettive,assenze,straordinari,ritardo,anticipo,richiestaassenza,richiestastraordinario,...
-1
votes
1answer
24 views

Single query to replace a value in multiple tables with same column

I am using Postgres SQL. I am having multiple tables say table1, table2, table3, table4, ... with a common column say name in each of the tables. I want to replace a the value 'Jhon' with 'Jimmy' in ...
0
votes
1answer
287 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
2answers
291 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 ...
8
votes
1answer
15k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
2
votes
1answer
76 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 ...
1
vote
0answers
33 views

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback. I'd like to ...
1
vote
1answer
26 views

SQL Server 2019 UPDATE Statement SET to function does not execute the function again for each row

Here is the scenario. I have a local SQL Server to which I have restored the live SQL Server databases. In order to be GDPR/CCPA compliant, I must anonymize the PII in the local server's databases. I ...
1
vote
1answer
28 views

How do you combine multiple update statements for the same row using MySQL trigger

Background: Each time a column is modified, I need to update the associated column (which has the same name) in a second table. This is my first attempt at using a trigger. Code: Here's a simplified ...
0
votes
1answer
61 views

Does update lock the row or the entire table?

Basic question: When I update a row in a database table, does it lock the current row or the entire table? I am using MySQL. When I update column which is not unique. Is the locking different from one ...
2
votes
1answer
77 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 ...
2
votes
1answer
43 views

MySQL Best approach for recording last update timestamp for every column and every row

This question is about possible approaches for time stamping every column of every row in a table. I have a table with a small number of rows (fewer than 200 and not likely to grow much larger). Each ...
-1
votes
1answer
41 views

How to update records of two tables through trigger?

I have two tables as employee and employee_backup whenever i update employee table the trigger must update employee_backup table also : this is the piece of code i was trying to achieve specified goal:...
2
votes
1answer
25 views

Mysql How to update n rows using count function?

In the following table, I want to set delete = true if the total records for same orgid exceed 500 and I want to do it according to createdate such that if records exceed 500 the old records get ...
0
votes
1answer
2k views

SQL Server updates missing from WSUS

I don't use WSUS for SQL Server patching because we have so many different compatibility issues, requirements and downtime OLA's that it makes it impossible to deploy anything in this way - not sure ...
0
votes
1answer
150 views

I Want To insert data in transaction table and at the same time update customer table in codeigniter

(codeigniter) I Want To insert data in transaction table and at the same time update customer table . I want to Add value of ChangeDue in transation table To value now_debt in Customer Table ...
2
votes
1answer
82 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, ...
1
vote
1answer
23 views

Delete duplicate records and update the mapping table

I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map and retain the latest row based on updated_date because i want to put constraints in store_id and ...
0
votes
2answers
43 views

Fastest way to update column value

I need to set all values of a given column foo to a constant varchar bar. Is there a faster way to do that than with the vanilla UPDATE mytable SET foo = 'bar'? Facts: This is an Oracle MySQL 8.0.20 ...
1
vote
1answer
79 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 <...
0
votes
2answers
97 views

Update a table with data from another table

I have a Wordpress table in which I want to take the user_email from Table2 and import it to contact_email on Table1 one based off of the user_login. user_login and user_id equal the same value. ...
1
vote
1answer
15 views

Mass update all cells in a column with a substring of it's original value

I need to trim the last seven characters off of all the cells in a column. This query returns the results that I need SELECT test_date, SUBSTRING(test_date FROM 1 FOR CHAR_LENGTH(test_date) - 7) as ...
1
vote
1answer
54 views

Update dynamic jsonb array of objects

I am trying to update a jsonb key with a new value, but I cannot update all the keys at once. My json structure is something like this: [{ "type": "button", "content": { "align": "...
7
votes
3answers
29k views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY (`...
0
votes
1answer
15 views

Update main and second table with a before update trigger for message logging

I currently have the following testing schema: CREATE TABLE userMessage ( message_id BIGSERIAL PRIMARY KEY, creator_id BIGINT NOT NULL REFERENCES "User" (id), created_at BIGINT ...

1
2 3 4 5
17