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
0answers
14 views

Can you use a SELECT … FOR UPDATE cursor in a stored procedure to enforce row-level locking?

From https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/re37.html: CREATE PROCEDURE needy_bonus( ) BEGIN DECLARE v_employee_id INT; DECLARE v_salary ...
0
votes
1answer
28 views

PostgreSQL update column in one table with value from another, based on key from first table

Sorry about the title, I wasn't sure how best to word this. My situation is this: I have two tables: paper_author [paper_id, author_id, author_name] authors [author_id, author_name] Right now, the ...
0
votes
0answers
8 views

How to apply many UPDATE statements in one network call

Can we apply many UPDATE statements in a single network call? Figure many UPDATE requests on a JSONB field of one record. I would like to update many places in the JSONB field. All updates apply on ...
0
votes
2answers
46 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
1answer
22 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
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
36 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
36 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
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: ...
0
votes
2answers
19 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} ...
8
votes
2answers
368 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 ...
0
votes
2answers
57 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 ...
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
44 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
112 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
36 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
1answer
34 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'; ...
-1
votes
1answer
26 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
0answers
27 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
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
35 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
77 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 ...
1
vote
0answers
35 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 ...
2
votes
1answer
45 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:...
1
vote
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 ...
1
vote
1answer
24 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
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 ...
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
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
vote
1answer
58 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": "...
0
votes
0answers
18 views

Which aspects of PostgreSQL `UPDATE` do impact IOPS?

Premises: there is a big table (hundreds of millions of rows), a new column is added to the table and needs to be populated with values. UPDATE by chunks (e.g. id ranges of different sizes) causes ...
0
votes
0answers
18 views

How does Postgresql UPDATE work internally?

How does Postgresql UPDATE work internally? In some questions on SO/DBA I found that technically it works like DELETE/INSERT though in the docs it is only said about partitioned tables: In the ...
0
votes
1answer
31 views

Why do these queries deadlock and how to work this around in MariaDB?

The following 2 queries enounter DB deadlock in my program: DELETE FROM proxies WHERE NOT EXISTS ( SELECT 1 FROM proxy_xref_proxy_list AS pxpl WHERE pxpl.ProxyID=proxies.ID AND (pxpl....
0
votes
1answer
39 views

Update a Column with sequential number based on the order by of a column in Oracle Sql

I have table with below create scripts create table training( action_id NUMBER not null, training_id NUMBER, course_name VARCHAR2(100), display_order NUMBER default 0 )...
1
vote
1answer
20 views

UPDATE table element using data and id from another table

I need to update an element the row for id=N in table A with derived data from Table B. Table A has uid as its primary key, and contains DATE element foo. Table B has a_uid as an element, for each ...
0
votes
0answers
11 views

mysql queries in 2 times (select) + 1 time (Insert) verifications handle problems for db to dont create double inserts

I want to make an 2 way select verification for all users in that kind of code version: select * from table_A where other_user_id='B' if (1) -> update set response_A='1' if (0) -> select * from ...
0
votes
0answers
91 views

Bulk INSERT … ON DUPLICATE KEY UPDATE inserts only a small fraction of rows in MariaDB

I have a table like the following: CREATE TABLE `demo_upsert` ( `ID` BIGINT(20) NOT NULL AUTO_INCREMENT, `Url` TEXT NOT NULL COLLATE 'utf8mb4_unicode_ci', `LastListID` BIGINT(20) NULL ...
0
votes
2answers
44 views

Postgres- update a value in a array on particular index

I have a column named ids that is of type bigint[]. ids cane be null or of any length It has values based on certain hierarchy. My use case is I want to update value that is at a particular index. ...
0
votes
1answer
45 views

Update SQLITE table [closed]

I want to update my sqlite table and I use a request with conditionals my purpose is to set the name to the new value when the value is not null and keep the old value if the new value is null . I ...
0
votes
1answer
23 views

Update large number of row in MySQL table

I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount |id|customer_id |type |amount| ...
1
vote
1answer
28 views

Create a key-preserved view across Oracle tables with descending indexes

I'm trying to create an updatable view between two Oracle tables which have exactly the same key structure. The existing tables are created by an ERP system and: Do not have primary keys - only a ...
0
votes
3answers
41 views

update one column based on query containing another (joined) table

Consider the following select query: SELECT s.id, s.event, s.column, e.oldcolumn FROM section as s INNER JOIN event as e ON s.event = e.id WHERE s.id = 1 Now I wish to update the section table so ...
0
votes
1answer
21 views

How to add all combinations of two pairs of key-value to a table?

I have a database of items, as each item has a set of key-value pairs such place => london field => engineering since I frequently need to find items having the same two key-value sets. I ...
0
votes
1answer
27 views

UPDATE SET values ignoring set ordering

I'm trying to update 2 rows at once, I want to: 1) Set coupon.start (datetime) to the original account.vipExpires (datetime) 2) Add coupon.days (int) to account.vipExpires (datetime) 3) Set coupon....
-2
votes
1answer
38 views

Am not able to execute the update stmt using Multiple Joins..SQL DEVELOPER [closed]

UPDATE artikel A SET A.art_haccp = (SELECT DISTINCT a.art_haccp FROM artikel A, warengruppe W, sparte B, ...
0
votes
1answer
17 views

Bulk update an address column where the address was duplicated with a delimiter separating the duplicate?

Sorry if my title doesn't make much sense, I wasn't sure how to word it though. Basically I imported a file containing 500+ of stores and information about them. For some reason the [Address] column ...
0
votes
2answers
25 views

slow UPDATE of rows by COUNTing from another table

I try to update a table by counting rows from another table as UPDATE t1 SET c1 = ( SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id ) strangely, this query stuck at Sending data state ...

1
2 3 4 5
17